explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FCdR

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 145.692 53,162.359 ↓ 49.0 49 1

GroupAggregate (cost=9,685.42..9,685.45 rows=1 width=532) (actual time=52,910.164..53,162.359 rows=49 loops=1)

  • Group Key: alias_53718369.value_date, alias_53718369.quantity
2. 539.025 53,016.667 ↓ 900,407.0 900,407 1

Sort (cost=9,685.42..9,685.43 rows=1 width=532) (actual time=52,905.678..53,016.667 rows=900,407 loops=1)

  • Sort Key: alias_53718369.value_date, alias_53718369.quantity
  • Sort Method: external merge Disk: 29960kB
3. 96.258 52,477.642 ↓ 900,407.0 900,407 1

Subquery Scan on alias_53718369 (cost=9,685.37..9,685.41 rows=1 width=532) (actual time=51,980.907..52,477.642 rows=900,407 loops=1)

  • Filter: (alias_53718369.value_date <= '2019-05-01 23:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 18403
4. 314.471 52,381.384 ↓ 918,810.0 918,810 1

WindowAgg (cost=9,685.37..9,685.40 rows=1 width=552) (actual time=51,980.906..52,381.384 rows=918,810 loops=1)

5. 1,018.099 52,066.913 ↓ 918,810.0 918,810 1

Sort (cost=9,685.37..9,685.38 rows=1 width=552) (actual time=51,980.894..52,066.913 rows=918,810 loops=1)

  • Sort Key: measurement.physical_meter_id, measurement.quantity_id, measurement.expected_time
  • Sort Method: external merge Disk: 51248kB
6. 1,938.917 51,048.814 ↓ 918,810.0 918,810 1

Nested Loop (cost=5,716.78..9,685.36 rows=1 width=552) (actual time=26.483..51,048.814 rows=918,810 loops=1)

  • Join Filter: (timezone((((- (logical_meter.utc_offset)::integer))::character varying)::text, date_trunc('hour'::text, timezone((((- (logical_meter.utc_offset)::integer))::character varying)::text, measurement.expected_time))) = measurement.expected_time)
7. 11.332 154.213 ↓ 111.1 18,446 1

Nested Loop (cost=5,716.08..8,228.30 rows=166 width=574) (actual time=26.432..154.213 rows=18,446 loops=1)

  • Join Filter: (display_quantity.quantity_id = quantity.id)
  • Rows Removed by Join Filter: 4766
8. 0.014 0.014 ↑ 1.0 1 1

Index Scan using quantity_pkey on quantity (cost=0.14..12.35 rows=1 width=520) (actual time=0.010..0.014 rows=1 loops=1)

  • Filter: ((name)::text = 'Volume'::text)
  • Rows Removed by Filter: 11
9. 19.835 142.867 ↓ 11.6 23,212 1

Hash Join (cost=5,715.94..8,191.01 rows=1,995 width=54) (actual time=26.418..142.867 rows=23,212 loops=1)

  • Hash Cond: (meter_definition.id = display_quantity.meter_definition_id)
10. 15.505 123.018 ↓ 29.8 20,659 1

Hash Join (cost=5,714.43..8,179.95 rows=694 width=66) (actual time=26.397..123.018 rows=20,659 loops=1)

  • Hash Cond: (meter_definition.medium_id = medium.id)
11. 19.210 107.506 ↓ 29.8 20,659 1

Hash Join (cost=5,713.25..8,175.98 rows=694 width=74) (actual time=26.384..107.506 rows=20,659 loops=1)

  • Hash Cond: (logical_meter.meter_definition_id = meter_definition.id)
12. 46.396 88.289 ↓ 29.8 20,659 1

Hash Join (cost=5,712.07..8,172.01 rows=694 width=58) (actual time=26.371..88.289 rows=20,659 loops=1)

  • Hash Cond: (physical_meter.logical_meter_id = logical_meter.id)
13. 15.638 16.825 ↑ 1.0 20,659 1

Bitmap Heap Scan on physical_meter (cost=576.67..2,952.15 rows=20,678 width=62) (actual time=1.288..16.825 rows=20,659 loops=1)

  • Recheck Cond: (organisation_id = '8cf5a221-78fa-4628-b7d7-e025750002b1'::uuid)
  • Heap Blocks: exact=721
14. 1.187 1.187 ↑ 1.0 20,659 1

Bitmap Index Scan on physical_meter_organisation_id_manufacturer_idx (cost=0.00..571.50 rows=20,678 width=0) (actual time=1.187..1.187 rows=20,659 loops=1)

  • Index Cond: (organisation_id = '8cf5a221-78fa-4628-b7d7-e025750002b1'::uuid)
15. 3.860 25.068 ↓ 5.4 20,651 1

Hash (cost=5,087.98..5,087.98 rows=3,793 width=60) (actual time=25.068..25.068 rows=20,651 loops=1)

  • Buckets: 32768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2112kB
16. 5.967 21.208 ↓ 5.4 20,651 1

Hash Join (cost=3,027.09..5,087.98 rows=3,793 width=60) (actual time=11.929..21.208 rows=20,651 loops=1)

  • Hash Cond: (location.logical_meter_id = logical_meter.id)
17. 3.450 4.648 ↑ 1.0 20,651 1

Bitmap Heap Scan on location (cost=613.76..2,620.00 rows=20,819 width=32) (actual time=1.286..4.648 rows=20,651 loops=1)

  • Recheck Cond: (organisation_id = '8cf5a221-78fa-4628-b7d7-e025750002b1'::uuid)
  • Heap Blocks: exact=633
18. 1.198 1.198 ↑ 1.0 20,651 1

Bitmap Index Scan on location_organisation_id_lower_city_idx (cost=0.00..608.56 rows=20,819 width=0) (actual time=1.198..1.198 rows=20,651 loops=1)

  • Index Cond: (organisation_id = '8cf5a221-78fa-4628-b7d7-e025750002b1'::uuid)
19. 4.058 10.593 ↓ 1.0 20,651 1

Hash (cost=2,155.73..2,155.73 rows=20,608 width=44) (actual time=10.593..10.593 rows=20,651 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1789kB
20. 5.159 6.535 ↓ 1.0 20,651 1

Bitmap Heap Scan on logical_meter (cost=572.13..2,155.73 rows=20,608 width=44) (actual time=1.441..6.535 rows=20,651 loops=1)

  • Recheck Cond: (organisation_id = '8cf5a221-78fa-4628-b7d7-e025750002b1'::uuid)
  • Heap Blocks: exact=394
21. 1.376 1.376 ↓ 1.0 20,651 1

Bitmap Index Scan on logical_meter_organisation_id_meter_definition_id_idx (cost=0.00..566.98 rows=20,608 width=0) (actual time=1.376..1.376 rows=20,651 loops=1)

  • Index Cond: (organisation_id = '8cf5a221-78fa-4628-b7d7-e025750002b1'::uuid)
22. 0.002 0.007 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=16) (actual time=0.007..0.007 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.005 0.005 ↑ 1.0 8 1

Seq Scan on meter_definition (cost=0.00..1.08 rows=8 width=16) (actual time=0.003..0.005 rows=8 loops=1)

24. 0.002 0.007 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=8) (actual time=0.007..0.007 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.005 0.005 ↑ 1.0 8 1

Seq Scan on medium (cost=0.00..1.08 rows=8 width=8) (actual time=0.003..0.005 rows=8 loops=1)

26. 0.006 0.014 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=12) (actual time=0.014..0.014 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.008 0.008 ↑ 1.0 23 1

Seq Scan on display_quantity (cost=0.00..1.23 rows=23 width=12) (actual time=0.004..0.008 rows=23 loops=1)

28. 48,955.684 48,955.684 ↓ 50.0 50 18,446

Index Only Scan using measurement_organisation_id_physical_meter_id_quantity_id_e_idx on measurement (cost=0.70..8.73 rows=1 width=52) (actual time=1.704..2.654 rows=50 loops=18,446)

  • Index Cond: ((organisation_id = '8cf5a221-78fa-4628-b7d7-e025750002b1'::uuid) AND (physical_meter_id = physical_meter.id) AND (quantity_id = display_quantity.quantity_id) AND (expected_time <= '2019-05-02 00:00:00+00'::timestamp with time zone) AND (expected_time >= '2019-04-29 23:00:00+00'::timestamp with time zone))
  • Filter: range_contains_elem(physical_meter.active_period, expected_time)
  • Heap Fetches: 918810
Planning time : 6.621 ms
Execution time : 53,176.887 ms