explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 63kf

Settings
# exclusive inclusive rows x rows loops node
1. 80.584 100,941.081 ↑ 66.9 455 1

Unique (cost=571,688.45..572,145.14 rows=30,446 width=122) (actual time=100,798.629..100,941.081 rows=455 loops=1)

2. 301.232 100,860.497 ↓ 2.8 86,534 1

Sort (cost=571,688.45..571,764.56 rows=30,446 width=122) (actual time=100,798.623..100,860.497 rows=86,534 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: 10259kB
3. 146.896 100,559.265 ↓ 2.8 86,534 1

Hash Join (cost=30,717.47..569,421.14 rows=30,446 width=122) (actual time=1,331.714..100,559.265 rows=86,534 loops=1)

  • Hash Cond: (g.area_to = (a_1.eid)::integer)
4. 133.361 100,397.136 ↓ 2.8 86,534 1

Hash Join (cost=30,521.29..568,425.75 rows=30,446 width=97) (actual time=1,316.469..100,397.136 rows=86,534 loops=1)

  • Hash Cond: ("*SELECT* 1".border_id = g.id)
5. 134.097 100,247.898 ↓ 2.8 86,534 1

Hash Join (cost=30,312.60..567,798.43 rows=30,446 width=84) (actual time=1,300.564..100,247.898 rows=86,534 loops=1)

  • Hash Cond: ("*SELECT* 1".data_source_id = f.id)
6. 102.152 100,113.635 ↓ 1.1 86,534 1

Append (cost=30,309.93..566,760.54 rows=82,287 width=56) (actual time=1,300.366..100,113.635 rows=86,534 loops=1)

7. 103.323 99,715.111 ↑ 1.0 80,406 1

Subquery Scan on *SELECT* 1 (cost=30,309.93..565,581.79 rows=82,284 width=56) (actual time=1,300.364..99,715.111 rows=80,406 loops=1)

8. 68,472.730 99,611.788 ↑ 1.0 80,406 1

Nested Loop (cost=30,309.93..564,758.95 rows=82,284 width=68) (actual time=1,300.361..99,611.788 rows=80,406 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: 47253258
9. 0.930 0.930 ↑ 3.0 336 1

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

10. 29,236.652 31,138.128 ↓ 8.6 140,874 336

Materialize (cost=30,309.91..32,602.36 rows=16,375 width=48) (actual time=3.871..92.673 rows=140,874 loops=336)

11. 184.854 1,901.476 ↓ 8.6 140,874 1

Subquery Scan on b (cost=30,309.91..32,520.48 rows=16,375 width=48) (actual time=1,300.214..1,901.476 rows=140,874 loops=1)

12. 272.968 1,716.622 ↓ 8.6 140,874 1

Unique (cost=30,309.91..32,356.73 rows=16,375 width=64) (actual time=1,300.210..1,716.622 rows=140,874 loops=1)

13. 504.875 1,443.654 ↓ 1.3 219,999 1

Sort (cost=30,309.91..30,719.27 rows=163,746 width=64) (actual time=1,300.205..1,443.654 rows=219,999 loops=1)

  • Sort Key: capacity.start_time, capacity.border_id, capacity.period, capacity.forecast_interval, capacity.dump_date DESC
  • Sort Method: quicksort Memory: 37082kB
14. 280.467 938.779 ↓ 1.3 219,999 1

Custom Scan (ConstraintAwareAppend) (cost=0.00..16,128.60 rows=163,746 width=64) (actual time=1.446..938.779 rows=219,999 loops=1)

  • Hypertable: capacity
  • Chunks left after exclusion: 25
15. 258.852 658.312 ↓ 1.3 219,999 1

Append (cost=0.00..16,128.60 rows=163,746 width=56) (actual time=1.441..658.312 rows=219,999 loops=1)

16. 0.031 0.031 ↓ 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.031..0.031 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.019 0.019 ↓ 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.019..0.019 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.021 0.021 ↓ 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.021..0.021 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.017 0.017 ↓ 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.017..0.017 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.014 0.014 ↓ 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.014..0.014 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.011 0.011 ↓ 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.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)))
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.013 0.013 ↓ 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.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)))
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.013 0.013 ↓ 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.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)))
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. 101.432 101.432 ↓ 1.4 29,380 1

Index Only Scan using _hyper_1_27_chunk_capacity_unique on _hyper_1_27_chunk (cost=0.43..6,970.12 rows=20,691 width=56) (actual time=1.200..101.432 rows=29,380 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: 26976
32. 107.361 107.361 ↓ 1.4 67,354 1

Index Only Scan using _hyper_1_28_chunk_capacity_unique on _hyper_1_28_chunk (cost=0.43..3,464.30 rows=49,229 width=56) (actual time=0.091..107.361 rows=67,354 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: 47195
33. 77.030 77.030 ↓ 1.3 49,609 1

Index Only Scan using _hyper_1_66_chunk_capacity_unique on _hyper_1_66_chunk (cost=0.42..2,364.87 rows=37,774 width=56) (actual time=0.079..77.030 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. 53.284 53.284 ↓ 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.136..53.284 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.123 0.123 ↑ 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.055..0.123 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.209 0.209 ↑ 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.089..0.209 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. 19.360 19.360 ↓ 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.078..19.360 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.222 0.222 ↑ 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.075..0.222 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. 18.126 18.126 ↓ 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.075..18.126 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. 22.094 22.094 ↓ 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.081..22.094 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. 7.829 296.372 ↓ 2,042.7 6,128 1

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

42. 42.018 288.543 ↓ 2,042.7 6,128 1

Nested Loop (cost=0.75..1,178.72 rows=3 width=68) (actual time=0.276..288.543 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. 16.989 229.750 ↓ 275.0 275 1

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

44. 9.640 91.281 ↓ 243.0 6,074 1

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

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

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

46. 60.944 60.944 ↓ 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.110..60.944 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: 38
47. 121.480 121.480 ↓ 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.020..0.020 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. 16.775 16.775 ↑ 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.061 rows=96 loops=275)

49. 0.081 0.166 ↑ 1.0 74 1

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

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

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

51. 0.248 15.877 ↑ 1.0 300 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
52. 6.741 15.629 ↑ 1.0 300 1

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

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

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

  • Merge Cond: (g.area_from = (a.eid)::integer)
54. 0.258 0.258 ↑ 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.015..0.258 rows=300 loops=1)

55. 0.239 0.239 ↓ 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.016..0.239 rows=327 loops=1)

56. 7.797 7.797 ↓ 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.015..7.797 rows=10,344 loops=1)

57. 0.263 15.233 ↓ 1.0 327 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
58. 6.793 14.970 ↓ 1.0 327 1

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

  • Merge Cond: ((a_1.eid)::integer = (e_1.eid)::integer)
59. 0.262 0.262 ↓ 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.262 rows=327 loops=1)

60. 7.915 7.915 ↓ 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.008..7.915 rows=10,344 loops=1)