explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fgsm : Optimization for: Optimization for: plan #FCdR; plan #mZcQ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 250.880 31,582.769 ↓ 97.0 97 1

GroupAggregate (cost=9,685.01..9,685.03 rows=1 width=532) (actual time=31,100.913..31,582.769 rows=97 loops=1)

  • Group Key: alias_130687704.value_date, alias_130687704.quantity
2. 1,007.423 31,331.889 ↓ 1,782,298.0 1,782,298 1

Sort (cost=9,685.01..9,685.01 rows=1 width=532) (actual time=31,095.903..31,331.889 rows=1,782,298 loops=1)

  • Sort Key: alias_130687704.value_date, alias_130687704.quantity
  • Sort Method: external merge Disk: 59304kB
3. 185.126 30,324.466 ↓ 1,782,298.0 1,782,298 1

Subquery Scan on alias_130687704 (cost=9,684.96..9,685.00 rows=1 width=532) (actual time=29,327.410..30,324.466 rows=1,782,298 loops=1)

  • Filter: (alias_130687704.value_date <= '2019-05-01 23:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 18403
4. 609.429 30,139.340 ↓ 1,800,701.0 1,800,701 1

WindowAgg (cost=9,684.96..9,684.98 rows=1 width=552) (actual time=29,327.408..30,139.340 rows=1,800,701 loops=1)

5. 1,700.992 29,529.911 ↓ 1,800,701.0 1,800,701 1

Sort (cost=9,684.96..9,684.96 rows=1 width=552) (actual time=29,327.396..29,529.911 rows=1,800,701 loops=1)

  • Sort Key: measurement.physical_meter_id, measurement.quantity_id, measurement.expected_time
  • Sort Method: external merge Disk: 100440kB
6. 2,843.212 27,828.919 ↓ 1,800,701.0 1,800,701 1

Nested Loop (cost=5,716.78..9,684.95 rows=1 width=552) (actual time=32.363..27,828.919 rows=1,800,701 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. 8.563 120.499 ↓ 111.1 18,446 1

Nested Loop (cost=5,716.08..8,228.30 rows=166 width=560) (actual time=24.305..120.499 rows=18,446 loops=1)

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

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

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

Hash Join (cost=5,715.94..8,191.01 rows=1,995 width=40) (actual time=24.292..111.919 rows=23,212 loops=1)

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

Hash Join (cost=5,714.43..8,179.95 rows=694 width=52) (actual time=24.271..96.857 rows=20,659 loops=1)

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

Hash Join (cost=5,713.25..8,175.98 rows=694 width=60) (actual time=24.259..84.392 rows=20,659 loops=1)

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

Hash Join (cost=5,712.07..8,172.01 rows=694 width=44) (actual time=24.247..70.077 rows=20,659 loops=1)

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

Bitmap Heap Scan on physical_meter (cost=576.67..2,952.15 rows=20,678 width=48) (actual time=1.149..13.343 rows=20,659 loops=1)

  • Recheck Cond: (organisation_id = '8cf5a221-78fa-4628-b7d7-e025750002b1'::uuid)
  • Heap Blocks: exact=721
14. 1.060 1.060 ↑ 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.060..1.060 rows=20,659 loops=1)

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

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

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

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

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

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

  • Recheck Cond: (organisation_id = '8cf5a221-78fa-4628-b7d7-e025750002b1'::uuid)
  • Heap Blocks: exact=633
18. 1.031 1.031 ↑ 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.031..1.031 rows=20,651 loops=1)

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1789kB
20. 4.690 5.758 ↓ 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.120..5.758 rows=20,651 loops=1)

  • Recheck Cond: (organisation_id = '8cf5a221-78fa-4628-b7d7-e025750002b1'::uuid)
  • Heap Blocks: exact=394
21. 1.068 1.068 ↓ 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.068..1.068 rows=20,651 loops=1)

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

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

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

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

24. 0.002 0.006 ↑ 1.0 8 1

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

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

Seq Scan on medium (cost=0.00..1.08 rows=8 width=8) (actual time=0.003..0.004 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. 24,865.208 24,865.208 ↓ 98.0 98 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=0.641..1.348 rows=98 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-27 23:00:00+00'::timestamp with time zone))
  • Heap Fetches: 1800701
Planning time : 7.772 ms
Execution time : 31,608.712 ms