explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mZcQ : Optimization for: plan #FCdR

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 186.731 4,826.256 ↓ 73.0 73 1

GroupAggregate (cost=9,685.42..9,685.45 rows=1 width=532) (actual time=4,473.629..4,826.256 rows=73 loops=1)

  • Group Key: alias_53718369.value_date, alias_53718369.quantity
2. 752.988 4,639.525 ↓ 1,341,331.0 1,341,331 1

Sort (cost=9,685.42..9,685.43 rows=1 width=532) (actual time=4,468.690..4,639.525 rows=1,341,331 loops=1)

  • Sort Key: alias_53718369.value_date, alias_53718369.quantity
  • Sort Method: external merge Disk: 44632kB
3. 135.047 3,886.537 ↓ 1,341,331.0 1,341,331 1

Subquery Scan on alias_53718369 (cost=9,685.37..9,685.41 rows=1 width=532) (actual time=3,151.199..3,886.537 rows=1,341,331 loops=1)

  • Filter: (alias_53718369.value_date <= '2019-05-01 23:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 18403
4. 457.259 3,751.490 ↓ 1,359,734.0 1,359,734 1

WindowAgg (cost=9,685.37..9,685.40 rows=1 width=552) (actual time=3,151.197..3,751.490 rows=1,359,734 loops=1)

5. 968.676 3,294.231 ↓ 1,359,734.0 1,359,734 1

Sort (cost=9,685.37..9,685.38 rows=1 width=552) (actual time=3,151.186..3,294.231 rows=1,359,734 loops=1)

  • Sort Key: measurement.physical_meter_id, measurement.quantity_id, measurement.expected_time
  • Sort Method: external merge Disk: 75848kB
6. 956.253 2,325.555 ↓ 1,359,734.0 1,359,734 1

Nested Loop (cost=5,716.78..9,685.36 rows=1 width=552) (actual time=23.386..2,325.555 rows=1,359,734 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. 3.047 59.636 ↓ 111.1 18,446 1

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

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

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

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

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

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

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

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

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

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

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

  • Hash Cond: (physical_meter.logical_meter_id = logical_meter.id)
13. 5.078 6.143 ↑ 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.141..6.143 rows=20,659 loops=1)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

24. 0.005 0.013 ↑ 1.0 8 1

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

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

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

26. 0.013 0.029 ↑ 1.0 23 1

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

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

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

28. 1,309.666 1,309.666 ↓ 74.0 74 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.013..0.071 rows=74 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-28 23:00:00+00'::timestamp with time zone))
  • Filter: range_contains_elem(physical_meter.active_period, expected_time)
  • Heap Fetches: 1359734
Planning time : 8.945 ms
Execution time : 4,843.422 ms