explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MNmK

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 19.021 ↑ 1.0 2 1

Sort (cost=24,822.96..24,822.96 rows=2 width=49) (actual time=19.020..19.021 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. 1.137 13.764 ↑ 51.7 270 1

Nested Loop (cost=1.40..15,632.87 rows=13,962 width=38) (actual time=0.232..13.764 rows=270 loops=1)

  • Join Filter: ((loc.id)::text = (sk.id)::text)
4. 0.423 7.227 ↑ 51.7 270 1

Nested Loop (cost=0.97..8,490.46 rows=13,963 width=42) (actual time=0.187..7.227 rows=270 loops=1)

5. 0.143 0.594 ↑ 51.7 270 1

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

6. 0.109 0.109 ↑ 1.0 2 1

Seq Scan on wms_subarea_movementcontrol mcs (cost=0.00..5.04 rows=2 width=44) (actual time=0.052..0.109 rows=2 loops=1)

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

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.038..0.171 rows=135 loops=2)

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

Index Scan using pk_wms_location_id on wms_location loc (cost=0.42..0.47 rows=1 width=12) (actual time=0.023..0.023 rows=1 loops=270)

  • Index Cond: ((id)::text = (locsub.locations_id)::text)
  • Filter: ((blockreason)::text = 'NONE'::text)
9. 5.400 5.400 ↑ 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.020..0.020 rows=1 loops=270)

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

CTE cte_racksubarea_location_count

11. 0.414 14.626 ↑ 100.0 2 1

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

  • Group Key: crl.sub
12. 14.212 14.212 ↑ 51.7 270 1

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

13.          

CTE cte_racksubarea_orders

14. 0.001 1.983 ↓ 0.0 0 1

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

15. 0.008 1.982 ↓ 0.0 0 1

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

  • Sort Key: crl_1.sub, o.id, sk_1.id
  • Sort Method: quicksort Memory: 25kB
16. 0.000 1.974 ↓ 0.0 0 1

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

  • Join Filter: ((ol.header_id)::text = (o.id)::text)
17. 0.001 1.974 ↓ 0.0 0 1

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

18. 0.000 1.973 ↓ 0.0 0 1

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

19. 0.001 1.973 ↓ 0.0 0 1

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

20. 0.071 1.972 ↓ 0.0 0 1

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

  • Hash Cond: ((crl_1.loc)::text = (sk_1.parent_id)::text)
21. 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.000..0.047 rows=270 loops=1)

22. 0.072 1.854 ↓ 1.2 79 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
23. 0.161 1.782 ↓ 1.3 83 1

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

24. 0.044 0.044 ↓ 1.3 83 1

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

25. 1.577 1.577 ↑ 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.019..0.019 rows=1 loops=83)

  • Index Cond: ((id)::text = (malloc.destinationtransportunit_id)::text)
26. 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)
27. 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)
28. 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
29. 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
30.          

CTE cte_workstationsubarea_orders

31. 0.001 2.090 ↓ 0.0 0 1

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

32. 0.018 2.089 ↓ 0.0 0 1

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

  • Sort Key: locsub_1.subareas_id, op_1.id, tu.id, sk_2.id
  • Sort Method: quicksort Memory: 25kB
33. 0.001 2.071 ↓ 0.0 0 1

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

  • Join Filter: ((o_1.id)::text = (op_1.id)::text)
34. 0.000 2.070 ↓ 0.0 0 1

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

  • Join Filter: ((ol_1.header_id)::text = (o_1.id)::text)
35. 0.001 2.070 ↓ 0.0 0 1

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

  • Join Filter: ((alloc_1.orderline_id)::text = (ol_1.id)::text)
36. 0.000 2.069 ↓ 0.0 0 1

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

  • Join Filter: ((malloc_1.id)::text = (alloc_1.id)::text)
37. 0.096 2.069 ↓ 0.0 0 1

Nested Loop (cost=4.74..7,522.80 rows=1 width=78) (actual time=2.069..2.069 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
38. 0.023 0.313 ↑ 180.2 4 1

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

  • Hash Cond: ((loc_1.id)::text = (m.destinationstockkeepable_id)::text)
39. 0.007 0.231 ↑ 180.2 4 1

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

  • Join Filter: ((locsub_1.locations_id)::text = (sk_2.id)::text)
40. 0.010 0.148 ↑ 180.2 4 1

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

41. 0.066 0.066 ↑ 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.046..0.066 rows=4 loops=1)

  • Index Cond: (subareas_id = ANY ('{MCS-CONSOLIDATION-WS01,MCS-CONSOLIDATION-WS02}'::text[]))
  • Heap Fetches: 0
42. 0.072 0.072 ↑ 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.018..0.018 rows=1 loops=4)

  • Index Cond: ((id)::text = (locsub_1.locations_id)::text)
  • Filter: ((blockreason)::text = 'NONE'::text)
43. 0.076 0.076 ↑ 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.019..0.019 rows=1 loops=4)

  • Index Cond: ((id)::text = (loc_1.id)::text)
  • Filter: ((stockkeepabletype_id)::text <> 'LT_DROPOFF'::text)
44. 0.011 0.059 ↑ 5.1 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.048 0.048 ↓ 1.1 44 1

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

46. 0.079 1.660 ↓ 1.3 83 4

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

47. 0.126 1.581 ↓ 1.3 83 1

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

48. 0.044 0.044 ↓ 1.3 83 1

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

49. 1.411 1.411 ↑ 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.017..0.017 rows=1 loops=83)

  • Index Cond: ((id)::text = (malloc_1.destinationtransportunit_id)::text)
50. 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)

51. 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)

52. 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)

53. 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)

54.          

CTE cte_subarea_orders_grouped

55. 0.000 4.087 ↓ 0.0 0 1

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

56. 0.006 4.087 ↓ 0.0 0 1

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

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

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

58. 0.001 1.985 ↓ 0.0 0 1

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

  • Group Key: cro.sub, cro.orderid
59. 1.984 1.984 ↓ 0.0 0 1

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

60. 0.002 2.093 ↓ 0.0 0 1

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

  • Group Key: cwo.sub, cwo.orderid
61. 2.091 2.091 ↓ 0.0 0 1

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

62.          

CTE cte_subarea_orders_summed

63. 0.001 4.124 ↓ 0.0 0 1

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

  • Group Key: csog.sub, csog.orderid
64. 0.006 4.123 ↓ 0.0 0 1

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

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

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

66. 0.001 4.117 ↓ 0.0 0 1

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

67. 0.000 4.116 ↓ 0.0 0 1

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

68. 0.001 4.116 ↓ 0.0 0 1

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

69. 0.000 4.115 ↓ 0.0 0 1

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

70. 0.009 4.115 ↓ 0.0 0 1

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

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

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

72. 0.000 4.089 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
73. 4.089 4.089 ↓ 0.0 0 1

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

74. 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)
75. 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)
76. 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)
77. 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)
78. 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
79.          

CTE cte_subarea_orders_capacity

80. 0.004 4.137 ↓ 0.0 0 1

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

  • Group Key: csos.sub, csos.orderid, csos.tucount
81. 0.007 4.133 ↓ 0.0 0 1

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

  • Hash Cond: ((crl_2.sub)::text = (csos.sub)::text)
82. 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)

83. 0.000 4.125 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
84. 4.125 4.125 ↓ 0.0 0 1

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

85. 0.015 18.999 ↑ 1.0 2 1

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

  • Group Key: sub.id
86. 0.005 18.984 ↑ 1.0 2 1

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

  • Merge Cond: ((sub.id)::text = (csoc.sub)::text)
87. 0.072 14.831 ↑ 1.0 2 1

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

  • Sort Key: sub.id
  • Sort Method: quicksort Memory: 25kB
88. 0.023 14.759 ↑ 1.0 2 1

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

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

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

90. 0.011 0.106 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
91. 0.095 0.095 ↑ 1.0 2 1

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

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

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

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

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

Planning time : 19.734 ms
Execution time : 19.933 ms