explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n2G2

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 138.415 ↑ 1.0 2 1

Sort (cost=24,229.35..24,229.35 rows=2 width=49) (actual time=138.415..138.415 rows=2 loops=1)

  • Sort Key: (((((max(crlc.total_locs) - COALESCE(sum(csoc.required_locations), '0'::bigint)))::double precision / (max(crlc.total_locs))::double precision) * '100'::double precision)) DESC, sub.id
  • Sort Method: quicksort Memory: 25kB
2.          

CTE cte_racksubarea_locations

3. 63.947 137.949 ↑ 51.7 270 1

Gather (cost=3,079.65..15,039.26 rows=13,962 width=38) (actual time=106.460..137.949 rows=270 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 1.596 74.002 ↑ 64.6 90 3

Nested Loop (cost=2,079.65..12,643.06 rows=5,818 width=38) (actual time=48.524..74.002 rows=90 loops=3)

  • Join Filter: ((loc.id)::text = (sk.id)::text)
5. 20.549 72.390 ↑ 64.6 90 3

Hash Join (cost=2,079.23..9,667.01 rows=5,818 width=42) (actual time=48.461..72.390 rows=90 loops=3)

  • Hash Cond: ((loc.id)::text = (locsub.locations_id)::text)
6. 50.770 50.770 ↑ 1.1 97,355 3

Parallel Seq Scan on wms_location loc (cost=0.00..7,123.83 rows=109,216 width=12) (actual time=0.171..50.770 rows=97,355 loops=3)

  • Filter: ((blockreason)::text = 'NONE'::text)
  • Rows Removed by Filter: 58
7. 0.158 1.071 ↑ 51.7 270 3

Hash (cost=1,904.62..1,904.62 rows=13,969 width=30) (actual time=1.071..1.071 rows=270 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 146kB
8. 0.407 0.913 ↑ 51.7 270 3

Nested Loop (cost=0.55..1,904.62 rows=13,969 width=30) (actual time=0.330..0.913 rows=270 loops=3)

9. 0.194 0.194 ↑ 1.0 2 3

Seq Scan on wms_subarea_movementcontrol mcs (cost=0.00..5.04 rows=2 width=44) (actual time=0.115..0.194 rows=2 loops=3)

  • Filter: ((sourcesubarea_id)::text = ANY ('{MCS-CONSOLIDATION-WS01,MCS-CONSOLIDATION-WS02}'::text[]))
  • Rows Removed by Filter: 161
10. 0.312 0.312 ↑ 77.4 135 6

Index Only Scan using pk_subarea_location_id on wms_location_wms_subarea locsub (cost=0.55..845.34 rows=10,445 width=29) (actual time=0.119..0.312 rows=135 loops=6)

  • Index Cond: (subareas_id = (mcs.id)::text)
  • Heap Fetches: 0
11. 0.016 0.016 ↑ 1.0 1 270

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable sk (cost=0.42..0.50 rows=1 width=20) (actual time=0.016..0.016 rows=1 loops=270)

  • Index Cond: ((id)::text = (locsub.locations_id)::text)
  • Filter: ((stockkeepabletype_id)::text <> 'LT_DROPOFF'::text)
12.          

CTE cte_racksubarea_location_count

13. 0.184 132.715 ↑ 100.0 2 1

HashAggregate (cost=349.05..351.05 rows=200 width=98) (actual time=132.712..132.715 rows=2 loops=1)

  • Group Key: crl.sub
14. 132.531 132.531 ↑ 51.7 270 1

CTE Scan on cte_racksubarea_locations crl (cost=0.00..279.24 rows=13,962 width=180) (actual time=106.463..132.531 rows=270 loops=1)

15.          

CTE cte_racksubarea_orders

16. 0.001 2.285 ↓ 0.0 0 1

Unique (cost=912.15..912.16 rows=1 width=139) (actual time=2.285..2.285 rows=0 loops=1)

17. 0.015 2.284 ↓ 0.0 0 1

Sort (cost=912.15..912.15 rows=1 width=139) (actual time=2.284..2.284 rows=0 loops=1)

  • Sort Key: crl_1.sub, o.id, sk_1.id
  • Sort Method: quicksort Memory: 25kB
18. 0.001 2.269 ↓ 0.0 0 1

Nested Loop (cost=561.09..912.14 rows=1 width=139) (actual time=2.269..2.269 rows=0 loops=1)

  • Join Filter: ((ol.header_id)::text = (o.id)::text)
19. 0.000 2.268 ↓ 0.0 0 1

Nested Loop (cost=560.96..911.93 rows=1 width=229) (actual time=2.268..2.268 rows=0 loops=1)

20. 0.001 2.268 ↓ 0.0 0 1

Nested Loop (cost=560.81..910.42 rows=1 width=139) (actual time=2.268..2.268 rows=0 loops=1)

21. 0.000 2.267 ↓ 0.0 0 1

Nested Loop (cost=560.67..910.06 rows=1 width=139) (actual time=2.267..2.267 rows=0 loops=1)

22. 0.075 2.267 ↓ 0.0 0 1

Hash Join (cost=560.53..909.59 rows=1 width=139) (actual time=2.267..2.267 rows=0 loops=1)

  • Hash Cond: ((crl_1.loc)::text = (sk_1.parent_id)::text)
23. 0.047 0.047 ↑ 51.7 270 1

CTE Scan on cte_racksubarea_locations crl_1 (cost=0.00..279.24 rows=13,962 width=180) (actual time=0.001..0.047 rows=270 loops=1)

24. 0.088 2.145 ↓ 1.2 79 1

Hash (cost=559.70..559.70 rows=66 width=76) (actual time=2.145..2.145 rows=79 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
25. 0.237 2.057 ↓ 1.3 83 1

Nested Loop (cost=0.42..559.70 rows=66 width=76) (actual time=0.143..2.057 rows=83 loops=1)

26. 0.077 0.077 ↓ 1.3 83 1

Seq Scan on wms_olalloc_movement malloc (cost=0.00..2.66 rows=66 width=74) (actual time=0.048..0.077 rows=83 loops=1)

27. 1.743 1.743 ↑ 1.0 1 83

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable sk_1 (cost=0.42..8.44 rows=1 width=39) (actual time=0.021..0.021 rows=1 loops=83)

  • Index Cond: ((id)::text = (malloc.destinationtransportunit_id)::text)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_olalloc_id on wms_olalloc alloc (cost=0.14..0.46 rows=1 width=74) (never executed)

  • Index Cond: ((id)::text = (malloc.id)::text)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_wms_orderline_id on wms_orderline ol (cost=0.14..0.35 rows=1 width=74) (never executed)

  • Index Cond: ((id)::text = (alloc.orderline_id)::text)
30. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_wms_order_picking_id on wms_order_picking op (cost=0.15..1.50 rows=1 width=90) (never executed)

  • Index Cond: (id = (ol.header_id)::text)
  • Heap Fetches: 0
31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_wms_order_id on wms_order o (cost=0.14..0.20 rows=1 width=37) (never executed)

  • Index Cond: (id = (op.id)::text)
  • Heap Fetches: 0
32.          

CTE cte_workstationsubarea_orders

33. 0.001 2.959 ↓ 0.0 0 1

Unique (cost=7,548.06..7,548.07 rows=1 width=131) (actual time=2.959..2.959 rows=0 loops=1)

34. 0.040 2.958 ↓ 0.0 0 1

Sort (cost=7,548.06..7,548.06 rows=1 width=131) (actual time=2.958..2.958 rows=0 loops=1)

  • Sort Key: locsub_1.subareas_id, op_1.id, tu.id, sk_2.id
  • Sort Method: quicksort Memory: 25kB
35. 0.000 2.918 ↓ 0.0 0 1

Nested Loop (cost=4.74..7,548.05 rows=1 width=131) (actual time=2.918..2.918 rows=0 loops=1)

  • Join Filter: ((o_1.id)::text = (op_1.id)::text)
36. 0.001 2.918 ↓ 0.0 0 1

Nested Loop (cost=4.74..7,531.97 rows=1 width=115) (actual time=2.918..2.918 rows=0 loops=1)

  • Join Filter: ((ol_1.header_id)::text = (o_1.id)::text)
37. 0.000 2.917 ↓ 0.0 0 1

Nested Loop (cost=4.74..7,530.48 rows=1 width=78) (actual time=2.917..2.917 rows=0 loops=1)

  • Join Filter: ((alloc_1.orderline_id)::text = (ol_1.id)::text)
38. 0.001 2.917 ↓ 0.0 0 1

Nested Loop (cost=4.74..7,527.67 rows=1 width=78) (actual time=2.917..2.917 rows=0 loops=1)

  • Join Filter: ((malloc_1.id)::text = (alloc_1.id)::text)
39. 0.132 2.916 ↓ 0.0 0 1

Nested Loop (cost=4.74..7,522.80 rows=1 width=78) (actual time=2.916..2.916 rows=0 loops=1)

  • Join Filter: (((tu.parent_id)::text = (sk_2.id)::text) OR ((m.grouptransportunit_id IS NOT NULL) AND ((m.grouptransportunit_id)::text = (tu.id)::text)))
  • Rows Removed by Join Filter: 332
40. 0.031 0.556 ↑ 180.2 4 1

Hash Left Join (cost=4.32..6,130.18 rows=721 width=66) (actual time=0.334..0.556 rows=4 loops=1)

  • Hash Cond: ((loc_1.id)::text = (m.destinationstockkeepable_id)::text)
41. 0.012 0.366 ↑ 180.2 4 1

Nested Loop (cost=1.40..6,121.84 rows=721 width=41) (actual time=0.152..0.366 rows=4 loops=1)

  • Join Filter: ((locsub_1.locations_id)::text = (sk_2.id)::text)
42. 0.014 0.226 ↑ 180.2 4 1

Nested Loop (cost=0.97..5,616.77 rows=721 width=41) (actual time=0.106..0.226 rows=4 loops=1)

43. 0.088 0.088 ↑ 180.2 4 1

Index Only Scan using pk_subarea_location_id on wms_location_wms_subarea locsub_1 (cost=0.55..61.73 rows=721 width=29) (actual time=0.056..0.088 rows=4 loops=1)

  • Index Cond: (subareas_id = ANY ('{MCS-CONSOLIDATION-WS01,MCS-CONSOLIDATION-WS02}'::text[]))
  • Heap Fetches: 0
44. 0.124 0.124 ↑ 1.0 1 4

Index Scan using pk_wms_location_id on wms_location loc_1 (cost=0.42..7.70 rows=1 width=12) (actual time=0.031..0.031 rows=1 loops=4)

  • Index Cond: ((id)::text = (locsub_1.locations_id)::text)
  • Filter: ((blockreason)::text = 'NONE'::text)
45. 0.128 0.128 ↑ 1.0 1 4

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable sk_2 (cost=0.42..0.69 rows=1 width=12) (actual time=0.032..0.032 rows=1 loops=4)

  • Index Cond: ((id)::text = (loc_1.id)::text)
  • Filter: ((stockkeepabletype_id)::text <> 'LT_DROPOFF'::text)
46. 0.063 0.159 ↑ 5.1 8 1

Hash (cost=2.41..2.41 rows=41 width=74) (actual time=0.159..0.159 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.096 0.096 ↓ 1.1 44 1

Seq Scan on wms_instruction_movement m (cost=0.00..2.41 rows=41 width=74) (actual time=0.028..0.096 rows=44 loops=1)

48. 0.167 2.228 ↓ 1.3 83 4

Materialize (cost=0.42..560.03 rows=66 width=76) (actual time=0.021..0.557 rows=83 loops=4)

49. 0.179 2.061 ↓ 1.3 83 1

Nested Loop (cost=0.42..559.70 rows=66 width=76) (actual time=0.055..2.061 rows=83 loops=1)

50. 0.056 0.056 ↓ 1.3 83 1

Seq Scan on wms_olalloc_movement malloc_1 (cost=0.00..2.66 rows=66 width=74) (actual time=0.017..0.056 rows=83 loops=1)

51. 1.826 1.826 ↑ 1.0 1 83

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable tu (cost=0.42..8.44 rows=1 width=39) (actual time=0.022..0.022 rows=1 loops=83)

  • Index Cond: ((id)::text = (malloc_1.destinationtransportunit_id)::text)
52. 0.000 0.000 ↓ 0.0 0

Seq Scan on wms_olalloc alloc_1 (cost=0.00..3.83 rows=83 width=74) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Seq Scan on wms_orderline ol_1 (cost=0.00..2.36 rows=36 width=74) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Seq Scan on wms_order o_1 (cost=0.00..1.22 rows=22 width=37) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Seq Scan on wms_order_picking op_1 (cost=0.00..12.70 rows=270 width=90) (never executed)

56.          

CTE cte_subarea_orders_grouped

57. 0.001 5.263 ↓ 0.0 0 1

Unique (cost=0.11..0.13 rows=2 width=180) (actual time=5.263..5.263 rows=0 loops=1)

58. 0.011 5.262 ↓ 0.0 0 1

Sort (cost=0.11..0.12 rows=2 width=180) (actual time=5.262..5.262 rows=0 loops=1)

  • Sort Key: cro.sub, cro.orderid
  • Sort Method: quicksort Memory: 25kB
59. 0.003 5.251 ↓ 0.0 0 1

Append (cost=0.03..0.10 rows=2 width=180) (actual time=5.251..5.251 rows=0 loops=1)

60. 0.001 2.287 ↓ 0.0 0 1

HashAggregate (cost=0.03..0.04 rows=1 width=180) (actual time=2.287..2.287 rows=0 loops=1)

  • Group Key: cro.sub, cro.orderid
61. 2.286 2.286 ↓ 0.0 0 1

CTE Scan on cte_racksubarea_orders cro (cost=0.00..0.02 rows=1 width=180) (actual time=2.286..2.286 rows=0 loops=1)

62. 0.001 2.961 ↓ 0.0 0 1

HashAggregate (cost=0.03..0.04 rows=1 width=180) (actual time=2.961..2.961 rows=0 loops=1)

  • Group Key: cwo.sub, cwo.orderid
63. 2.960 2.960 ↓ 0.0 0 1

CTE Scan on cte_workstationsubarea_orders cwo (cost=0.00..0.02 rows=1 width=180) (actual time=2.960..2.960 rows=0 loops=1)

64.          

CTE cte_subarea_orders_summed

65. 0.002 5.313 ↓ 0.0 0 1

GroupAggregate (cost=32.21..32.28 rows=2 width=188) (actual time=5.313..5.313 rows=0 loops=1)

  • Group Key: csog.sub, csog.orderid
66. 0.010 5.311 ↓ 0.0 0 1

Sort (cost=32.21..32.22 rows=5 width=192) (actual time=5.311..5.311 rows=0 loops=1)

  • Sort Key: csog.sub, csog.orderid
  • Sort Method: quicksort Memory: 25kB
67. 0.000 5.301 ↓ 0.0 0 1

Nested Loop (cost=1.05..32.15 rows=5 width=192) (actual time=5.301..5.301 rows=0 loops=1)

68. 0.001 5.301 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.63..9.38 rows=5 width=252) (actual time=5.301..5.301 rows=0 loops=1)

69. 0.001 5.300 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.49..7.62 rows=5 width=254) (actual time=5.300..5.300 rows=0 loops=1)

70. 0.000 5.299 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.35..5.85 rows=5 width=254) (actual time=5.299..5.299 rows=0 loops=1)

71. 0.000 5.299 ↓ 0.0 0 1

Nested Loop (cost=0.21..4.09 rows=5 width=254) (actual time=5.299..5.299 rows=0 loops=1)

72. 0.014 5.299 ↓ 0.0 0 1

Hash Join (cost=0.07..2.58 rows=2 width=217) (actual time=5.299..5.299 rows=0 loops=1)

  • Hash Cond: ((ol_2.header_id)::text = (csog.orderid)::text)
73. 0.020 0.020 ↑ 36.0 1 1

Seq Scan on wms_orderline ol_2 (cost=0.00..2.36 rows=36 width=74) (actual time=0.020..0.020 rows=1 loops=1)

74. 0.001 5.265 ↓ 0.0 0 1

Hash (cost=0.04..0.04 rows=2 width=180) (actual time=5.265..5.265 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
75. 5.264 5.264 ↓ 0.0 0 1

CTE Scan on cte_subarea_orders_grouped csog (cost=0.00..0.04 rows=2 width=180) (actual time=5.264..5.264 rows=0 loops=1)

76. 0.000 0.000 ↓ 0.0 0

Index Scan using i_wms_olalloc_ol_id on wms_olalloc alloc_2 (cost=0.14..0.73 rows=2 width=111) (never executed)

  • Index Cond: ((orderline_id)::text = (ol_2.id)::text)
77. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_wms_instruction_movement_id on wms_instruction_movement mov (cost=0.14..0.35 rows=1 width=74) (never executed)

  • Index Cond: ((id)::text = (alloc_2.instruction_id)::text)
78. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_olalloc_stock_id on wms_olalloc_stock allocstock (cost=0.14..0.35 rows=1 width=74) (never executed)

  • Index Cond: ((id)::text = (alloc_2.id)::text)
79. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_wms_stock_id on wms_stock st (cost=0.14..0.35 rows=1 width=72) (never executed)

  • Index Cond: ((allocstock.stock_id)::text = (id)::text)
80. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_wms_stockkeepable_id on wms_stockkeepable sk_3 (cost=0.42..4.55 rows=1 width=12) (never executed)

  • Index Cond: ((id = (COALESCE(mov.grouptransportunit_id, st.stockkeepable_id))::text) AND (id IS NOT NULL))
  • Heap Fetches: 0
81.          

CTE cte_subarea_orders_capacity

82. 0.000 5.335 ↓ 0.0 0 1

HashAggregate (cost=334.46..334.51 rows=2 width=192) (actual time=5.335..5.335 rows=0 loops=1)

  • Group Key: csos.sub, csos.orderid, csos.tucount
83. 0.019 5.335 ↓ 0.0 0 1

Hash Join (cost=0.07..333.06 rows=140 width=196) (actual time=5.334..5.335 rows=0 loops=1)

  • Hash Cond: ((crl_2.sub)::text = (csos.sub)::text)
84. 0.001 0.001 ↑ 13,962.0 1 1

CTE Scan on cte_racksubarea_locations crl_2 (cost=0.00..279.24 rows=13,962 width=98) (actual time=0.001..0.001 rows=1 loops=1)

85. 0.001 5.315 ↓ 0.0 0 1

Hash (cost=0.04..0.04 rows=2 width=188) (actual time=5.314..5.315 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
86. 5.314 5.314 ↓ 0.0 0 1

CTE Scan on cte_subarea_orders_summed csos (cost=0.00..0.04 rows=2 width=188) (actual time=5.314..5.314 rows=0 loops=1)

87. 0.020 138.378 ↑ 1.0 2 1

GroupAggregate (cost=11.78..11.89 rows=2 width=49) (actual time=138.375..138.378 rows=2 loops=1)

  • Group Key: sub.id
88. 0.005 138.358 ↑ 1.0 2 1

Merge Left Join (cost=11.78..11.81 rows=2 width=37) (actual time=138.356..138.358 rows=2 loops=1)

  • Merge Cond: ((sub.id)::text = (csoc.sub)::text)
89. 0.024 133.010 ↑ 1.0 2 1

Sort (cost=11.73..11.74 rows=2 width=33) (actual time=133.009..133.010 rows=2 loops=1)

  • Sort Key: sub.id
  • Sort Method: quicksort Memory: 25kB
90. 0.046 132.986 ↑ 1.0 2 1

Hash Join (cost=7.19..11.72 rows=2 width=33) (actual time=132.980..132.986 rows=2 loops=1)

  • Hash Cond: ((crlc.sub)::text = (sub.id)::text)
91. 132.722 132.722 ↑ 100.0 2 1

CTE Scan on cte_racksubarea_location_count crlc (cost=0.00..4.00 rows=200 width=98) (actual time=132.719..132.722 rows=2 loops=1)

92. 0.023 0.218 ↑ 1.0 2 1

Hash (cost=7.16..7.16 rows=2 width=25) (actual time=0.218..0.218 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
93. 0.195 0.195 ↑ 1.0 2 1

Seq Scan on wms_subarea sub (cost=0.00..7.16 rows=2 width=25) (actual time=0.105..0.195 rows=2 loops=1)

  • Filter: ((id)::text = ANY ('{MCS-CONSOLIDATION-WS01,MCS-CONSOLIDATION-WS02}'::text[]))
  • Rows Removed by Filter: 169
94. 0.006 5.343 ↓ 0.0 0 1

Sort (cost=0.05..0.06 rows=2 width=94) (actual time=5.343..5.343 rows=0 loops=1)

  • Sort Key: csoc.sub
  • Sort Method: quicksort Memory: 25kB
95. 5.337 5.337 ↓ 0.0 0 1

CTE Scan on cte_subarea_orders_capacity csoc (cost=0.00..0.04 rows=2 width=94) (actual time=5.337..5.337 rows=0 loops=1)

Planning time : 33.674 ms
Execution time : 145.015 ms