explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bVmS

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.043 981,673.353 ↓ 384.0 384 1

GroupAggregate (cost=373,657.06..373,657.09 rows=1 width=52) (actual time=981,673.138..981,673.353 rows=384 loops=1)

  • Group Key: a.hour, a.border_id, a.area
2. 1.546 981,672.310 ↓ 384.0 384 1

Sort (cost=373,657.06..373,657.07 rows=1 width=24) (actual time=981,672.295..981,672.310 rows=384 loops=1)

  • Sort Key: a.hour, a.border_id, a.area
  • Sort Method: quicksort Memory: 43kB
3. 0.045 981,670.764 ↓ 384.0 384 1

Subquery Scan on a (cost=373,657.03..373,657.05 rows=1 width=24) (actual time=981,670.605..981,670.764 rows=384 loops=1)

4. 0.711 981,670.719 ↓ 384.0 384 1

Unique (cost=373,657.03..373,657.04 rows=1 width=52) (actual time=981,670.603..981,670.719 rows=384 loops=1)

5. 3.817 981,670.008 ↓ 648.0 648 1

Sort (cost=373,657.03..373,657.04 rows=1 width=52) (actual time=981,669.977..981,670.008 rows=648 loops=1)

  • Sort Key: f.start_time, nm.border_id_to, fp.priority, f.dump_date DESC, f2.dump_date DESC
  • Sort Method: quicksort Memory: 75kB
6. 2,224.772 981,666.191 ↓ 648.0 648 1

Nested Loop (cost=1,035.65..373,657.02 rows=1 width=52) (actual time=975,513.919..981,666.191 rows=648 loops=1)

  • Join Filter: (b.area_from = (a_1.eid)::integer)
  • Rows Removed by Join Filter: 238,464
7. 32.324 979,441.419 ↓ 648.0 648 1

Merge Join (cost=1,035.22..373,468.11 rows=1 width=48) (actual time=975,494.124..979,441.419 rows=648 loops=1)

  • Merge Cond: ((p.forecast_dumpdate = t.forecast_dumpdate) AND (p.region = t.region) AND (p.version = t.version) AND (p.trader = t.trader))
8. 688,387.175 979,409.095 ↓ 11,105.6 277,640 1

Nested Loop (cost=1,033.46..405,851.54 rows=25 width=61) (actual time=43,220.136..979,409.095 rows=277,640 loops=1)

  • Join Filter: ((f.start_time >= p.reference_date) AND (b.area_from = p.area_eid) AND (f.start_time < (p.reference_date + '1 day'::interval)))
  • Rows Removed by Join Filter: 5,406,917,204
9. 265.310 265.310 ↑ 1.0 13,106 1

Index Scan using params_pkey on params p (cost=0.29..479.26 rows=13,132 width=21) (actual time=0.598..265.310 rows=13,106 loops=1)

10. 236,357.593 290,756.610 ↓ 412,574.0 412,574 13,106

Materialize (cost=1,033.18..405,076.81 rows=1 width=44) (actual time=3.059..22.185 rows=412,574 loops=13,106)

11. 8,840.363 54,399.017 ↓ 412,574.0 412,574 1

Nested Loop (cost=1,033.18..405,076.81 rows=1 width=44) (actual time=40,080.171..54,399.017 rows=412,574 loops=1)

  • Join Filter: (nm.border_id_from = b.id)
  • Rows Removed by Join Filter: 145,226,048
12. 1.886 1.886 ↓ 1.0 353 1

Index Scan using border_area_from_area_to_key on border b (cost=0.27..13.52 rows=350 width=6) (actual time=0.009..1.886 rows=353 loops=1)

13. 6,471.511 45,556.768 ↓ 412,574.0 412,574 353

Materialize (cost=1,032.91..405,058.04 rows=1 width=44) (actual time=0.029..129.056 rows=412,574 loops=353)

14. 399.141 39,085.257 ↓ 412,574.0 412,574 1

Gather (cost=1,032.91..405,058.03 rows=1 width=44) (actual time=9.901..39,085.257 rows=412,574 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
15. 33,727.003 38,686.116 ↓ 82,515.0 82,515 5 / 5

Nested Loop (cost=32.91..404,057.93 rows=1 width=44) (actual time=3.442..38,686.116 rows=82,515 loops=5)

  • Join Filter: ((bc.border_id_from = f.border_id) AND (fp.data_source_id = f.data_source_id) AND (fp.flow_id = f.flow_id))
  • Rows Removed by Join Filter: 1,781,013
16. 92.255 4,959.113 ↓ 11,953.7 83,676 5 / 5

Hash Join (cost=32.91..403,800.28 rows=7 width=56) (actual time=3.003..4,959.113 rows=83,676 loops=5)

  • Hash Cond: ((nm.border_id_to = fp.border_id) AND (f2.data_source_id = fp.data_source_id) AND (f2.flow_id = fp.flow_id))
17. 469.181 4,866.858 ↓ 1.0 226,031 5 / 5

Hash Join (cost=21.65..397,030.91 rows=225,268 width=48) (actual time=1.457..4,866.858 rows=226,031 loops=5)

  • Hash Cond: (f2.border_id = br.border_id2)
18. 3,311.418 4,397.677 ↑ 2.1 3,324,160 5 / 5

Append (cost=0.00..366,903.61 rows=7,107,575 width=32) (actual time=0.497..4,397.677 rows=3,324,160 loops=5)

19. 0.000 0.000 ↓ 0.0 0 5 / 5

Parallel Seq Scan on flow f2 (cost=0.00..0.00 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=5)

  • Filter: (period = '01:00:00'::interval)
20. 164.667 164.667 ↑ 2.0 132,452 5 / 5

Parallel Seq Scan on _hyper_9_145_chunk f2_1 (cost=0.00..10,712.91 rows=264,035 width=32) (actual time=0.496..164.667 rows=132,452 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 11,139
21. 166.267 166.267 ↑ 2.1 120,278 5 / 5

Parallel Seq Scan on _hyper_9_146_chunk f2_2 (cost=0.00..9,454.54 rows=248,913 width=32) (actual time=1.151..166.267 rows=120,278 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 9,537
22. 128.216 128.216 ↑ 2.1 117,348 5 / 5

Parallel Seq Scan on _hyper_9_147_chunk f2_3 (cost=0.00..9,245.56 rows=244,578 width=32) (actual time=0.392..128.216 rows=117,348 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 9,709
23. 126.208 126.208 ↑ 2.1 112,584 5 / 5

Parallel Seq Scan on _hyper_9_148_chunk f2_4 (cost=0.00..8,945.88 rows=233,940 width=32) (actual time=0.155..126.208 rows=112,584 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 10,101
24. 93.765 93.765 ↑ 2.1 113,715 5 / 5

Parallel Seq Scan on _hyper_9_149_chunk f2_5 (cost=0.00..8,997.05 rows=235,858 width=32) (actual time=0.240..93.765 rows=113,715 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 9,683
25. 173.089 173.089 ↑ 2.0 116,288 5 / 5

Parallel Seq Scan on _hyper_9_150_chunk f2_6 (cost=0.00..10,174.99 rows=232,495 width=32) (actual time=0.213..173.089 rows=116,288 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 17,442
26. 111.112 111.112 ↑ 2.0 129,613 5 / 5

Parallel Seq Scan on _hyper_9_151_chunk f2_7 (cost=0.00..10,779.65 rows=255,211 width=32) (actual time=0.063..111.112 rows=129,613 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 10,893
27. 3.458 3.458 ↑ 2.7 278 5 / 5

Parallel Seq Scan on _hyper_9_152_chunk f2_8 (cost=0.00..184.29 rows=741 width=32) (actual time=3.203..3.458 rows=278 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 2,016
28. 14.895 14.895 ↑ 2.9 16,116 5 / 5

Parallel Seq Scan on _hyper_9_153_chunk f2_9 (cost=0.00..1,507.08 rows=46,707 width=32) (actual time=1.410..14.895 rows=16,116 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 2,016
29. 58.712 58.712 ↑ 2.9 54,483 5 / 5

Parallel Seq Scan on _hyper_9_154_chunk f2_10 (cost=0.00..4,708.81 rows=159,546 width=32) (actual time=1.127..58.712 rows=54,483 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 2,016
30. 5.870 5.870 ↓ 0.0 0 5 / 5

Parallel Index Scan using "155_824_flow_new_pkey1" on _hyper_9_155_chunk f2_11 (cost=0.29..155.65 rows=1 width=32) (actual time=5.870..5.870 rows=0 loops=5)

  • Index Cond: (period = '01:00:00'::interval)
31. 2.228 2.228 ↓ 0.0 0 5 / 5

Parallel Seq Scan on _hyper_9_156_chunk f2_12 (cost=0.00..161.04 rows=1 width=32) (actual time=2.228..2.228 rows=0 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 2,016
32. 3.302 3.302 ↓ 0.0 0 5 / 5

Parallel Seq Scan on _hyper_9_157_chunk f2_13 (cost=0.00..161.04 rows=1 width=32) (actual time=3.302..3.302 rows=0 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 2,016
33. 2.808 2.808 ↓ 0.0 0 5 / 5

Parallel Seq Scan on _hyper_9_158_chunk f2_14 (cost=0.00..161.04 rows=1 width=32) (actual time=2.808..2.808 rows=0 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 2,016
34. 2.630 2.630 ↓ 0.0 0 5 / 5

Parallel Seq Scan on _hyper_9_159_chunk f2_15 (cost=0.00..161.04 rows=1 width=32) (actual time=2.630..2.630 rows=0 loops=5)

  • Filter: (period = '01:00:00'::interval)
  • Rows Removed by Filter: 2,016
35. 29.032 29.032 ↑ 2.1 79,106 5 / 5

Parallel Seq Scan on _hyper_9_160_chunk f2_16 (cost=0.00..5,924.78 rows=164,471 width=32) (actual time=0.143..29.032 rows=79,106 loops=5)