explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sHfd

Settings
# exclusive inclusive rows x rows loops node
1. 0.042 48,046.863 ↑ 112.2 6 1

Sort (cost=61,043,292.01..61,043,293.69 rows=673 width=146) (actual time=48,046.863..48,046.863 rows=6 loops=1)

  • Sort Key: report.route_post_stop_index
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=55,357,723 read=1
2. 0.055 48,046.821 ↑ 112.2 6 1

Subquery Scan on report (cost=61,038,510.53..61,043,260.40 rows=673 width=146) (actual time=48,046.725..48,046.821 rows=6 loops=1)

  • Filter: ((report.route_post_stop_index IS NOT NULL) AND (report.row_number = 1))
  • Rows Removed by Filter: 758
  • Buffers: shared hit=55,357,720 read=1
3. 0.650 48,046.766 ↑ 177.1 764 1

WindowAgg (cost=61,038,510.53..61,041,555.36 rows=135,326 width=136) (actual time=48,046.028..48,046.766 rows=764 loops=1)

  • Buffers: shared hit=55,357,720 read=1
4. 4.342 48,046.116 ↑ 177.1 764 1

Sort (cost=61,038,510.53..61,038,848.84 rows=135,326 width=132) (actual time=48,046.007..48,046.116 rows=764 loops=1)

  • Sort Key: c.barcode, s.post_index
  • Sort Method: quicksort Memory: 206kB
  • Buffers: shared hit=55,357,720 read=1
5. 611.451 48,041.774 ↑ 177.1 764 1

Hash Right Join (cost=60,971,618.77..61,026,976.64 rows=135,326 width=132) (actual time=47,990.286..48,041.774 rows=764 loops=1)

  • Hash Cond: (moved_to_cell.container_id = c.id)
  • Buffers: shared hit=55,357,717 read=1
6. 1,471.130 46,722.885 ↓ 2.3 3,877,941 1

GroupAggregate (cost=60,123,931.58..60,156,105.13 rows=1,652,922 width=16) (actual time=44,311.324..46,722.885 rows=3,877,941 loops=1)

  • Group Key: moved_to_cell.container_id
  • Buffers: shared hit=53,311,221
7. 3,090.322 45,251.755 ↓ 2.8 5,878,643 1

Sort (cost=60,123,931.58..60,129,146.36 rows=2,085,910 width=16) (actual time=44,311.309..45,251.755 rows=5,878,643 loops=1)

  • Sort Key: moved_to_cell.container_id
  • Sort Method: quicksort Memory: 472,170kB
  • Buffers: shared hit=53,311,221
8. 10,694.378 42,161.433 ↓ 2.8 5,878,643 1

Index Scan using idx_container_assignment_cell_id on container_assignment moved_to_cell (cost=0.44..59,904,991.91 rows=2,085,910 width=16) (actual time=0.037..42,161.433 rows=5,878,643 loops=1)

  • Index Cond: (cell_id IS NOT NULL)
  • Filter: (created_at > COALESCE((SubPlan 1), '1970-01-01 00:00:00+03'::timestamp with time zone))
  • Rows Removed by Filter: 414,768
  • Buffers: shared hit=53,311,218
9.          

SubPlan (for Index Scan)

10. 0.000 31,467.055 ↑ 1.0 1 6,293,411

Aggregate (cost=9.45..9.46 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=6,293,411)

  • Buffers: shared hit=49,386,410
11. 31,467.055 31,467.055 ↓ 0.0 0 6,293,411

Index Scan using idx_container_assignment_container_id_sc_module_id on container_assignment sent (cost=0.56..9.45 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=6,293,411)

  • Index Cond: (container_id = moved_to_cell.container_id)
  • Filter: (sending_session_id IS NOT NULL)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=49,386,410
12. 0.269 707.438 ↑ 177.1 764 1

Hash (cost=845,995.61..845,995.61 rows=135,326 width=132) (actual time=707.438..707.438 rows=764 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 2,172kB
  • Buffers: shared hit=2,046,496 read=1
13. 5.641 707.169 ↑ 177.1 764 1

Gather (cost=4,004.54..845,995.61 rows=135,326 width=132) (actual time=33.976..707.169 rows=764 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
  • Buffers: shared hit=2,046,496 read=1
14. 0.329 701.528 ↑ 213.1 127 6 / 6

Hash Left Join (cost=3,004.54..831,463.01 rows=27,065 width=132) (actual time=28.961..701.528 rows=127 loops=6)

  • Hash Cond: (c.to_post_index = pindex_to.index)
  • Buffers: shared hit=2,046,496 read=1
15. 0.112 681.008 ↑ 213.1 127 6 / 6

Hash Left Join (cost=813.86..828,907.45 rows=27,065 width=111) (actual time=8.541..681.008 rows=127 loops=6)

  • Hash Cond: (c.priority_rule_id = rule_.id)
  • Buffers: shared hit=2,040,214 read=1
16. 0.092 680.606 ↑ 213.1 127 6 / 6

Hash Left Join (cost=739.56..828,653.77 rows=27,065 width=78) (actual time=8.205..680.606 rows=127 loops=6)

  • Hash Cond: (c.to_post_index = (unnest(s.route_stop_indexes)))
  • Buffers: shared hit=2,039,778 read=1
17. 0.000 673.164 ↑ 213.1 127 6 / 6

Nested Loop (cost=0.56..827,799.58 rows=27,065 width=53) (actual time=0.806..673.164 rows=127 loops=6)

  • Buffers: shared hit=2,039,203
18. 427.221 427.221 ↑ 1.4 63,052 6 / 6

Parallel Seq Scan on session_container c (cost=0.00..655,082.68 rows=89,934 width=61) (actual time=0.019..427.221 rows=63,052 loops=6)

  • Filter: ((container_type_id = ANY ('{1,6}'::integer[])) AND (to_post_index <> ALL ('{140801,140802,140803,140804,140805,140806,140807,140808,140809,140810,140811,140812,140813,140814,140815,140816,140817,140818,140819,140820,140821,140822,140823,140824,140825,140826,140827,140828,140829,140830,140831,140832,140833,140834,140835,140836,140837,140838,140839,140840,140841,140842,140843,140844,140845,140846,140847,140848,140849,140850,140851,140852,140853,140854,140855,140856,140857,140858,140859,140860,140861,140862,140863,140864,140865,140866,140867,140868,140869,140870,140871,140872,140873,140874,140875,140876,140877,140878,140879,140880,140881,140882,140883,140884,140885,140886,140887,140888,140889,140890,140891,140892,140893,140894,140895,140896,140897,140898,140899,140911,140912,140913,140914,140915,140916,140925,140945,140946,140947,140948,140949,140951,140952,140953,140954,140955,140960,140961,140962,140963,140964,140965,140966,140967,140971,140972,140973,140974,140975,140976,140977,140978,140979,140980,140983,140987,140988,140989,140990,140991,140992,140993,140994,140995,140996,140997,140998,140999,142159,145701,145702,145703,145704,145705,145706,145707,145708,145709,145710,145711,145712,145713,145714,145715,145716,145717,145718,145719,145720,145721,145722,145723,145724,145725,145726,145727,145728,145729,145730,145731,145732,145733,145734,145735,145736,145737,145738,145739,145740,145741,145742,145743,145744,145745,145746,145747,145748,145749,145750,145751,145752,145753,145754,145755,145756,145757,145758,145759,145760,145761,145762,145763,145764,145765,145766,145767,145768,145769,145770,145771,145772,145773,145774,145775,145776,145777,145778,145779,145780,145781,145782,145783,145784,145785,145786,145787,145788,145789,145790,145791,145792,145793,145794,145795,145796,145797,145798,145799,145800,145801,145802,145803,145804,145805,145806,145807,145808,145809,145810,145811,145812,145813,145814,145815,145816,145817,145818,145819,145820,145821,145822,145823,145824,145825,145826,145827,145828,145829,145830,145831,145832,145833,145834,145835,145836,145837,145838,145839,145840,145841,145842,145843,145844,145845,145846,145847,145848,145849,145850,145851,145852,145853,145854,145855,145856,145857,145858,145859,145860,145861,145862,145863,145864,145865,145866,145867,145868,145869,145870,145871,145872,145873,145874,145875,145876,145877,145878,145879,145880,145881,145882,145883,145884,145885,145886,145887,145888,145889,145890,145891,145892,145893,145894,145895,145896,145897,145898,145899,145900,145901,145902,145903,145904,145905,145906,145907,145908,145909,145910,145911,145912,145913,145914,145915,145916,145917,145918,145932,145940,145941,145942,145943,145944,145945,145946,145947,145948,145949,145950,145951,145952,145953,145954,145955,145956,145957,145958,145959,145960,145961,145962,145963,145964,145965,145966,145967,145968,145969,145970,145971,145972,145973,145974,145975,145976,145977,145978,145979}'::integer[])))
  • Rows Removed by Filter: 761,293
  • Buffers: shared hit=143,205
19. 252.209 252.209 ↓ 0.0 0 378,314 / 6

Index Scan using container_assignment_pkey on container_assignment a (cost=0.56..1.91 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=378,314)

  • Index Cond: (id = c.assignment_id)
  • Filter: ((cell_id IS NOT NULL) AND ((wanted IS NULL) OR (NOT wanted)) AND (sc_workstation_id = ANY ('{99,5,104,97,9,105,102,29,100,72,101,98,70,6,103,7,15,2,88,106,3,107,108,109,110,20,4,1,18,17,8,26,27,30,25,31,33,32,34,35,68,71,12,91,16,11,14,13,73,74,75,76,77,78,79,80,81,82,83,84,85,86,92,87,10,89,90,93,94,95,96}'::bigint[])))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1,895,998
20. 0.015 7.350 ↑ 5.9 17 6 / 6

Hash (cost=737.75..737.75 rows=100 width=29) (actual time=7.350..7.350 rows=17 loops=6)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=440 read=1
21. 0.014 7.335 ↑ 5.9 17 6 / 6

Nested Loop Left Join (cost=16.78..737.75 rows=100 width=29) (actual time=7.277..7.335 rows=17 loops=6)

  • Buffers: shared hit=440 read=1
22. 0.004 0.079 ↑ 5.9 17 6 / 6

Unique (cost=16.49..17.00 rows=100 width=68) (actual time=0.072..0.079 rows=17 loops=6)

  • Buffers: shared hit=130
23. 0.045 0.075 ↓ 17.0 17 6 / 6

Sort (cost=16.49..16.50 rows=1 width=68) (actual time=0.072..0.075 rows=17 loops=6)

  • Sort Key: r.name, s.post_index, (unnest(s.route_stop_indexes))
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=130
24. 0.019 0.030 ↓ 17.0 17 6 / 6

Nested Loop Left Join (cost=0.42..16.48 rows=1 width=68) (actual time=0.026..0.030 rows=17 loops=6)

  • Join Filter: (s.route_id = r.id)
  • Buffers: shared hit=55
25. 0.007 0.007 ↑ 1.0 1 6 / 6

Index Scan using route_pkey on route r (cost=0.15..8.18 rows=1 width=64) (actual time=0.007..0.007 rows=1 loops=6)

  • Index Cond: (id = 397)
  • Filter: (output AND ((virtual IS NULL) OR (NOT virtual)) AND (route_transportation_type_code = ANY ('{0,2}'::integer[])) AND ((end_date IS NULL) OR (end_date > ('now'::cstring)::date)))
  • Buffers: shared hit=17
26. 0.004 0.004 ↑ 1.0 1 6 / 6

Index Scan using idx_route_stop_route_id on route_stop s (cost=0.27..8.29 rows=1 width=80) (actual time=0.003..0.004 rows=1 loops=6)

  • Index Cond: (route_id = 397)
  • Buffers: shared hit=23
27. 7.242 7.242 ↑ 1.0 1 102 / 6

Index Scan using idx_ops_index on ops_post_index_dictionary pindex (cost=0.29..7.19 rows=1 width=25) (actual time=0.426..0.426 rows=1 loops=102)

  • Index Cond: (index = s.post_index)
  • Buffers: shared hit=310 read=1
28. 0.073 0.290 ↓ 1.0 320 6 / 6

Hash (cost=70.39..70.39 rows=313 width=45) (actual time=0.290..0.290 rows=320 loops=6)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
  • Buffers: shared hit=366
29. 0.217 0.217 ↓ 1.0 320 6 / 6

Seq Scan on post_preprocessing_rule rule_ (cost=0.00..70.39 rows=313 width=45) (actual time=0.010..0.217 rows=320 loops=6)

  • Filter: active
  • Rows Removed by Filter: 626
  • Buffers: shared hit=366
30. 10.725 20.191 ↑ 1.0 50,830 6 / 6

Hash (cost=1,555.30..1,555.30 rows=50,830 width=25) (actual time=20.191..20.191 rows=50,830 loops=6)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,447kB
  • Buffers: shared hit=6,282
31. 9.466 9.466 ↑ 1.0 50,830 6 / 6

Seq Scan on ops_post_index_dictionary pindex_to (cost=0.00..1,555.30 rows=50,830 width=25) (actual time=0.006..9.466 rows=50,830 loops=6)

  • Buffers: shared hit=6,282
Planning time : 3.506 ms
Execution time : 48,094.955 ms