explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GqHO

Settings
# exclusive inclusive rows x rows loops node
1. 78.534 98,802.818 ↑ 87.0 355 1

Unique (cost=579,719.29..580,182.34 rows=30,870 width=122) (actual time=98,665.178..98,802.818 rows=355 loops=1)

2. 271.105 98,724.284 ↓ 2.7 82,789 1

Sort (cost=579,719.29..579,796.47 rows=30,870 width=122) (actual time=98,665.172..98,724.284 rows=82,789 loops=1)

  • Sort Key: f.name, e.name, e_1.name, (CASE WHEN ("*SELECT* 1".forecast_interval = '1 day'::interval) THEN 'daily'::text WHEN ("*SELECT* 1".forecast_interval = '7 days'::interval) THEN 'weekly'::text WHEN ("*SELECT* 1".forecast_interval = '1 mon'::interval) THEN 'monthly'::text WHEN ("*SELECT* 1".forecast_interval = '6 mons'::interval) THEN 'half-yearly'::text WHEN ("*SELECT* 1".forecast_interval = '00:00:00'::interval) THEN 'outage_message'::text ELSE 'other'::text END), "*SELECT* 1".db
  • Sort Method: quicksort Memory: 9966kB
3. 143.568 98,453.179 ↓ 2.7 82,789 1

Hash Join (cost=31,229.47..577,417.33 rows=30,870 width=122) (actual time=1,293.535..98,453.179 rows=82,789 loops=1)

  • Hash Cond: (g.area_to = (a_1.eid)::integer)
4. 128.364 98,293.586 ↓ 2.7 82,789 1

Hash Join (cost=31,033.29..576,410.81 rows=30,870 width=97) (actual time=1,277.496..98,293.586 rows=82,789 loops=1)

  • Hash Cond: ("*SELECT* 1".border_id = g.id)
5. 127.292 98,149.207 ↓ 2.7 82,789 1

Hash Join (cost=30,824.60..575,777.66 rows=30,870 width=84) (actual time=1,261.451..98,149.207 rows=82,789 loops=1)

  • Hash Cond: ("*SELECT* 1".data_source_id = f.id)
6. 97.460 98,021.786 ↑ 1.0 82,789 1

Append (cost=30,821.94..574,725.36 rows=83,432 width=56) (actual time=1,261.279..98,021.786 rows=82,789 loops=1)

7. 99.281 97,254.199 ↑ 1.1 76,661 1

Subquery Scan on *SELECT* 1 (cost=30,821.94..573,546.61 rows=83,429 width=56) (actual time=1,261.276..97,254.199 rows=76,661 loops=1)

8. 66,744.001 97,154.918 ↑ 1.1 76,661 1

Nested Loop (cost=30,821.94..572,712.32 rows=83,429 width=68) (actual time=1,261.273..97,154.918 rows=76,661 loops=1)

  • Join Filter: (((a_2.a = b.start_time) AND (b.period = '00:15:00'::interval)) OR ((date_trunc('hour'::text, a_2.a) = b.start_time) AND (b.period = '01:00:00'::interval)) OR ((date_trunc('day'::text, a_2.a) = b.start_time) AND (b.period = '1 day'::interval)))
  • Rows Removed by Join Filter: 45998683
9. 0.901 0.901 ↑ 3.0 336 1

Function Scan on generate_series a_2 (cost=0.03..10.03 rows=1,000 width=8) (actual time=0.073..0.901 rows=336 loops=1)

10. 28,564.633 30,410.016 ↓ 8.3 137,129 336

Materialize (cost=30,821.91..33,146.30 rows=16,603 width=48) (actual time=3.755..90.506 rows=137,129 loops=336)

11. 182.943 1,845.383 ↓ 8.3 137,129 1

Subquery Scan on b (cost=30,821.91..33,063.29 rows=16,603 width=48) (actual time=1,261.170..1,845.383 rows=137,129 loops=1)

12. 264.991 1,662.440 ↓ 8.3 137,129 1

Unique (cost=30,821.91..32,897.26 rows=16,603 width=64) (actual time=1,261.166..1,662.440 rows=137,129 loops=1)

13. 499.216 1,397.449 ↓ 1.3 213,247 1

Sort (cost=30,821.91..31,236.98 rows=166,028 width=64) (actual time=1,261.159..1,397.449 rows=213,247 loops=1)

  • Sort Key: capacity.start_time, capacity.border_id, capacity.period, capacity.forecast_interval, capacity.dump_date DESC
  • Sort Method: quicksort Memory: 36132kB
14. 294.922 898.233 ↓ 1.3 213,247 1

Custom Scan (ConstraintAwareAppend) (cost=0.00..16,426.40 rows=166,028 width=64) (actual time=0.819..898.233 rows=213,247 loops=1)

  • Hypertable: capacity
  • Chunks left after exclusion: 25
15. 257.562 603.311 ↓ 1.3 213,247 1

Append (cost=0.00..16,426.40 rows=166,028 width=56) (actual time=0.815..603.311 rows=213,247 loops=1)

16. 0.020 0.020 ↓ 0.0 0 1

Index Scan using _hyper_1_9_chunk_capacity_start_time_idx on _hyper_1_9_chunk (cost=0.29..2.21 rows=1 width=56) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
17. 0.012 0.012 ↓ 0.0 0 1

Index Scan using _hyper_1_10_chunk_capacity_start_time_idx on _hyper_1_10_chunk (cost=0.30..1.58 rows=1 width=56) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
18. 0.013 0.013 ↓ 0.0 0 1

Index Scan using _hyper_1_11_chunk_capacity_start_time_idx on _hyper_1_11_chunk (cost=0.43..1.73 rows=1 width=56) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
19. 0.011 0.011 ↓ 0.0 0 1

Index Scan using _hyper_1_12_chunk_capacity_start_time_idx on _hyper_1_12_chunk (cost=0.29..1.99 rows=1 width=56) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
20. 0.009 0.009 ↓ 0.0 0 1

Index Scan using _hyper_1_13_chunk_capacity_start_time_idx on _hyper_1_13_chunk (cost=0.29..2.04 rows=1 width=56) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
21. 0.012 0.012 ↓ 0.0 0 1

Index Scan using _hyper_1_14_chunk_capacity_start_time_idx on _hyper_1_14_chunk (cost=0.28..2.29 rows=1 width=56) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
22. 0.011 0.011 ↓ 0.0 0 1

Index Scan using _hyper_1_15_chunk_capacity_start_time_idx on _hyper_1_15_chunk (cost=0.29..2.29 rows=1 width=56) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
23. 0.011 0.011 ↓ 0.0 0 1

Index Scan using _hyper_1_16_chunk_capacity_start_time_idx on _hyper_1_16_chunk (cost=0.29..1.97 rows=1 width=56) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
24. 0.010 0.010 ↓ 0.0 0 1

Index Scan using _hyper_1_17_chunk_capacity_start_time_idx on _hyper_1_17_chunk (cost=0.28..2.23 rows=1 width=56) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
25. 0.010 0.010 ↓ 0.0 0 1

Index Scan using _hyper_1_18_chunk_capacity_start_time_idx on _hyper_1_18_chunk (cost=0.28..2.31 rows=1 width=56) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
26. 0.012 0.012 ↓ 0.0 0 1

Index Scan using _hyper_1_21_chunk_capacity_start_time_idx on _hyper_1_21_chunk (cost=0.43..2.03 rows=1 width=56) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
27. 0.012 0.012 ↓ 0.0 0 1

Index Scan using _hyper_1_23_chunk_capacity_start_time_idx on _hyper_1_23_chunk (cost=0.43..1.89 rows=1 width=56) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
28. 0.013 0.013 ↓ 0.0 0 1

Index Scan using _hyper_1_24_chunk_capacity_start_time_idx on _hyper_1_24_chunk (cost=0.43..1.76 rows=1 width=56) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
29. 0.011 0.011 ↓ 0.0 0 1

Index Scan using _hyper_1_25_chunk_capacity_start_time_idx on _hyper_1_25_chunk (cost=0.43..1.65 rows=1 width=56) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
30. 0.013 0.013 ↓ 0.0 0 1

Index Scan using _hyper_1_26_chunk_capacity_start_time_idx on _hyper_1_26_chunk (cost=0.43..2.42 rows=1 width=56) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Filter: ((capacity_type_id = 1) AND ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval)))
31. 70.251 70.251 ↓ 1.0 22,088 1

Index Only Scan using _hyper_1_27_chunk_capacity_unique on _hyper_1_27_chunk (cost=0.43..7,154.44 rows=21,235 width=56) (actual time=0.616..70.251 rows=22,088 loops=1)

  • Index Cond: ((start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone)) AND (capacity_type_id = 1))
  • Filter: ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval))
  • Heap Fetches: 27789
32. 97.819 97.819 ↓ 1.3 67,894 1

Index Only Scan using _hyper_1_28_chunk_capacity_unique on _hyper_1_28_chunk (cost=0.43..3,571.97 rows=50,889 width=56) (actual time=0.057..97.819 rows=67,894 loops=1)

  • Index Cond: ((start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone)) AND (capacity_type_id = 1))
  • Filter: ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval))
  • Heap Fetches: 47735
33. 82.480 82.480 ↓ 1.3 49,609 1

Index Only Scan using _hyper_1_66_chunk_capacity_unique on _hyper_1_66_chunk (cost=0.42..2,370.67 rows=37,852 width=56) (actual time=0.059..82.480 rows=49,609 loops=1)

  • Index Cond: ((start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone)) AND (capacity_type_id = 1))
  • Filter: ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval))
  • Heap Fetches: 29083
34. 42.933 42.933 ↓ 1.3 31,248 1

Index Only Scan using _hyper_1_70_chunk_capacity_unique on _hyper_1_70_chunk (cost=0.42..1,504.74 rows=23,894 width=56) (actual time=0.090..42.933 rows=31,248 loops=1)

  • Index Cond: ((start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone)) AND (capacity_type_id = 1))
  • Filter: ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval))
  • Heap Fetches: 18726
35. 0.118 0.118 ↑ 1.0 80 1

Index Only Scan using _hyper_1_85_chunk_capacity_unique on _hyper_1_85_chunk (cost=0.15..4.35 rows=80 width=56) (actual time=0.049..0.118 rows=80 loops=1)

  • Index Cond: ((start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone)) AND (capacity_type_id = 1))
  • Filter: ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval))
  • Heap Fetches: 0
36. 0.160 0.160 ↑ 1.0 128 1

Index Only Scan using _hyper_1_86_chunk_capacity_unique on _hyper_1_86_chunk (cost=0.28..7.80 rows=128 width=56) (actual time=0.042..0.160 rows=128 loops=1)

  • Index Cond: ((start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone)) AND (capacity_type_id = 1))
  • Filter: ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval))
  • Heap Fetches: 0
37. 17.344 17.344 ↓ 1.3 13,016 1

Index Only Scan using _hyper_1_87_chunk_capacity_unique on _hyper_1_87_chunk (cost=0.42..560.36 rows=9,829 width=56) (actual time=0.039..17.344 rows=13,016 loops=1)

  • Index Cond: ((start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone)) AND (capacity_type_id = 1))
  • Filter: ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval))
  • Heap Fetches: 4098
38. 0.200 0.200 ↑ 1.0 160 1

Index Only Scan using _hyper_1_88_chunk_capacity_unique on _hyper_1_88_chunk (cost=0.28..8.68 rows=160 width=56) (actual time=0.055..0.200 rows=160 loops=1)

  • Index Cond: ((start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone)) AND (capacity_type_id = 1))
  • Filter: ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval))
  • Heap Fetches: 0
39. 15.832 15.832 ↓ 1.3 13,680 1

Index Only Scan using _hyper_1_89_chunk_capacity_unique on _hyper_1_89_chunk (cost=0.42..563.62 rows=10,272 width=56) (actual time=0.040..15.832 rows=13,680 loops=1)

  • Index Cond: ((start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone)) AND (capacity_type_id = 1))
  • Filter: ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval))
  • Heap Fetches: 2728
40. 18.432 18.432 ↓ 1.3 15,344 1

Index Only Scan using _hyper_1_90_chunk_capacity_unique on _hyper_1_90_chunk (cost=0.42..649.38 rows=11,673 width=56) (actual time=0.056..18.432 rows=15,344 loops=1)

  • Index Cond: ((start_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone)) AND (capacity_type_id = 1))
  • Filter: ((period = '00:15:00'::interval) OR (period = '01:00:00'::interval) OR (period = '1 day'::interval))
  • Heap Fetches: 4235
41. 8.028 670.127 ↓ 2,042.7 6,128 1

Subquery Scan on *SELECT* 2 (cost=0.75..1,178.75 rows=3 width=56) (actual time=1.004..670.127 rows=6,128 loops=1)

42. 42.780 662.099 ↓ 2,042.7 6,128 1

Nested Loop (cost=0.75..1,178.72 rows=3 width=68) (actual time=1.001..662.099 rows=6,128 loops=1)

  • Join Filter: ((c.c >= timezone('Europe/Amsterdam'::text, b_1.start_time)) AND (c.c < timezone('Europe/Amsterdam'::text, b_1.end_time)))
  • Rows Removed by Join Filter: 20272
43. 21.133 601.994 ↓ 275.0 275 1

Nested Loop (cost=0.72..1,148.69 rows=1 width=24) (actual time=0.891..601.994 rows=275 loops=1)

44. 10.092 143.533 ↓ 243.0 6,074 1

Subquery Scan on a_3 (cost=0.29..1,086.69 rows=25 width=20) (actual time=0.196..143.533 rows=6,074 loops=1)

  • Filter: (a_3.status_id = 1)
  • Rows Removed by Filter: 1313
45. 22.706 133.441 ↓ 1.5 7,387 1

Unique (cost=0.29..1,024.96 rows=4,939 width=770) (actual time=0.192..133.441 rows=7,387 loops=1)

46. 110.735 110.735 ↓ 3.0 19,545 1

Index Scan Backward using transmission_message_pkey on transmission_message (cost=0.29..1,008.74 rows=6,487 width=770) (actual time=0.186..110.735 rows=19,545 loops=1)

  • Filter: (timezone('Europe/Amsterdam'::text, publication_time) <= (timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone) - '10:00:00'::interval))
  • Rows Removed by Filter: 40
47. 437.328 437.328 ↓ 0.0 0 6,074

Index Scan using transmission_entry_pkey on transmission_entry b_1 (cost=0.43..2.47 rows=1 width=28) (actual time=0.072..0.072 rows=0 loops=6,074)

  • Index Cond: ((message_id = a_3.id) AND (publication_time = a_3.publication_time) AND (start_time <= (timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone) + '1 day'::interval)))
  • Filter: (end_time >= timezone('Europe/Amsterdam'::text, ((now())::date)::timestamp with time zone))
  • Rows Removed by Filter: 11
48. 17.325 17.325 ↑ 10.4 96 275

Function Scan on generate_series c (cost=0.03..10.03 rows=1,000 width=8) (actual time=0.001..0.063 rows=96 loops=275)

49. 0.065 0.129 ↑ 1.0 74 1

Hash (cost=1.74..1.74 rows=74 width=36) (actual time=0.129..0.129 rows=74 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
50. 0.064 0.064 ↑ 1.0 74 1

Seq Scan on data_source f (cost=0.00..1.74 rows=74 width=36) (actual time=0.012..0.064 rows=74 loops=1)

51. 0.243 16.015 ↑ 1.0 300 1

Hash (cost=204.94..204.94 rows=300 width=19) (actual time=16.015..16.015 rows=300 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
52. 6.817 15.772 ↑ 1.0 300 1

Merge Left Join (cost=0.61..204.94 rows=300 width=19) (actual time=0.066..15.772 rows=300 loops=1)

  • Merge Cond: ((a.eid)::integer = (e.eid)::integer)
53. 0.588 1.090 ↑ 1.0 300 1

Merge Join (cost=0.32..22.19 rows=300 width=10) (actual time=0.044..1.090 rows=300 loops=1)

  • Merge Cond: (g.area_from = (a.eid)::integer)
54. 0.270 0.270 ↑ 1.0 300 1

Index Scan using border_area_from_area_to_key on border g (cost=0.15..8.65 rows=300 width=10) (actual time=0.025..0.270 rows=300 loops=1)

55. 0.232 0.232 ↓ 1.0 327 1

Index Scan using common_area_eid_index on area a (cost=0.15..9.04 rows=326 width=8) (actual time=0.013..0.232 rows=327 loops=1)

56. 7.865 7.865 ↓ 1.2 10,344 1

Index Scan using common_eic_eid_index on eic e (cost=0.29..319.56 rows=8,618 width=17) (actual time=0.014..7.865 rows=10,344 loops=1)

57. 0.284 16.025 ↓ 1.0 327 1

Hash (cost=192.11..192.11 rows=326 width=17) (actual time=16.025..16.025 rows=327 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
58. 7.256 15.741 ↓ 1.0 327 1

Merge Left Join (cost=0.43..192.11 rows=326 width=17) (actual time=0.029..15.741 rows=327 loops=1)

  • Merge Cond: ((a_1.eid)::integer = (e_1.eid)::integer)
59. 0.267 0.267 ↓ 1.0 327 1

Index Scan using common_area_eid_index on area a_1 (cost=0.15..9.04 rows=326 width=8) (actual time=0.012..0.267 rows=327 loops=1)

60. 8.218 8.218 ↓ 1.2 10,344 1

Index Scan using common_eic_eid_index on eic e_1 (cost=0.29..319.56 rows=8,618 width=17) (actual time=0.010..8.218 rows=10,344 loops=1)