explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wpz

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 1,640.959 ↓ 0.0 0 1

Subquery Scan on odata (cost=612,726.99..612,727.69 rows=3 width=37) (actual time=1,640.959..1,640.959 rows=0 loops=1)

2.          

CTE cte_viable_orders

3. 0.023 1,407.331 ↑ 289.8 83 1

Group (cost=463,643.16..469,376.42 rows=24,054 width=37) (actual time=1,406.974..1,407.331 rows=83 loops=1)

  • Group Key: auolpck_3.orderid
4. 68.456 1,407.308 ↑ 240.5 200 1

Gather Merge (cost=463,643.16..469,256.15 rows=48,108 width=37) (actual time=1,406.973..1,407.308 rows=200 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.305 1,338.852 ↑ 359.0 67 3 / 3

Sort (cost=462,643.14..462,703.27 rows=24,054 width=37) (actual time=1,338.846..1,338.852 rows=67 loops=3)

  • Sort Key: auolpck_3.orderid
  • Sort Method: quicksort Memory: 30kB
  • Worker 0: Sort Method: quicksort Memory: 30kB
  • Worker 1: Sort Method: quicksort Memory: 29kB
6. 0.702 1,338.547 ↑ 359.0 67 3 / 3

Partial HashAggregate (cost=460,652.19..460,892.73 rows=24,054 width=37) (actual time=1,338.389..1,338.547 rows=67 loops=3)

  • Group Key: auolpck_3.orderid
7. 1,238.958 1,337.845 ↑ 98.6 1,318 3 / 3

Parallel Seq Scan on wms_audit_orderline_picking auolpck_3 (cost=93,782.58..460,327.14 rows=130,019 width=37) (actual time=305.985..1,337.845 rows=1,318 loops=3)

  • Filter: ((NOT (hashed SubPlan 1)) AND ((revtype)::text = 'PROCESSED'::text) AND ((ordertype)::text <> ALL ('{STOCKTAKE,CONSOLIDATE,STOCK_MOVE,BULKSTORAGE}'::text[])))
  • Rows Removed by Filter: 937345
8.          

SubPlan (for Parallel Seq Scan)

9. 89.657 98.887 ↓ 1.0 31,761 3 / 3

Bitmap Heap Scan on wms_audit_orderline_picking auolpck2 (cost=665.22..93,704.91 rows=31,070 width=37) (actual time=12.057..98.887 rows=31,761 loops=3)

  • Recheck Cond: ((revtype)::text = 'OUTBOUND_PICK_ORDER_CONFIRM_INTERFACE_SENT'::text)
  • Heap Blocks: exact=14743
10. 9.230 9.230 ↓ 1.0 31,761 3 / 3

Bitmap Index Scan on i_audit_olp_revtype (cost=0.00..657.45 rows=31,070 width=0) (actual time=9.230..9.230 rows=31,761 loops=3)

  • Index Cond: ((revtype)::text = 'OUTBOUND_PICK_ORDER_CONFIRM_INTERFACE_SENT'::text)
11. 0.255 1,640.958 ↓ 0.0 0 1

GroupAggregate (cost=143,350.57..143,351.24 rows=3 width=94) (actual time=1,640.958..1,640.958 rows=0 loops=1)

  • Group Key: oldata.orderid, oldata.ordertype
  • Filter: ((count(*) <= sum(CASE WHEN ((((oldata.requestedquantity)::numeric - oldata.pickedquantity) - oldata.shortquantity) <= '0'::numeric) THEN 1 ELSE 0 END)) AND (bool_and((oldata.istupackedsent OR ((oldata.requestedquantity)::numeric = oldata.shortquantity))) OR (oldata.ordertype = ANY ('{DISASSEMBLY,CLEANOUT,VAS,RELOCATE}'::text[]))))
  • Rows Removed by Filter: 12
12. 0.135 1,640.703 ↓ 28.4 398 1

Sort (cost=143,350.57..143,350.61 rows=14 width=142) (actual time=1,640.688..1,640.703 rows=398 loops=1)

  • Sort Key: oldata.orderid, oldata.ordertype
  • Sort Method: quicksort Memory: 80kB
13. 0.032 1,640.568 ↓ 28.4 398 1

Subquery Scan on oldata (cost=143,349.64..143,350.30 rows=14 width=142) (actual time=1,639.525..1,640.568 rows=398 loops=1)

14. 0.986 1,640.536 ↓ 28.4 398 1

GroupAggregate (cost=143,349.64..143,350.16 rows=14 width=179) (actual time=1,639.525..1,640.536 rows=398 loops=1)

  • Group Key: auolpck.orderid, auolpck.orderlineid
15. 4.077 1,639.550 ↓ 89.5 1,253 1

Sort (cost=143,349.64..143,349.67 rows=14 width=188) (actual time=1,639.504..1,639.550 rows=1,253 loops=1)

  • Sort Key: auolpck.orderid, auolpck.orderlineid
  • Sort Method: quicksort Memory: 381kB
16. 0.563 1,635.473 ↓ 89.5 1,253 1

Nested Loop Left Join (cost=143,052.10..143,349.37 rows=14 width=188) (actual time=1,616.843..1,635.473 rows=1,253 loops=1)

17. 0.360 1,619.874 ↓ 89.5 1,253 1

Nested Loop (cost=143,051.55..143,332.02 rows=14 width=151) (actual time=1,616.796..1,619.874 rows=1,253 loops=1)

  • Join Filter: ((auolpck_1.orderid)::text = (auolpck.orderid)::text)
18. 0.101 1,617.042 ↓ 12.0 12 1

Nested Loop (cost=143,050.99..143,176.01 rows=1 width=180) (actual time=1,616.729..1,617.042 rows=12 loops=1)

  • Join Filter: ((auolpck_1.orderid)::text = (cte_viable_orders.orderid)::text)
  • Rows Removed by Join Filter: 984
19. 0.050 1,616.749 ↓ 12.0 12 1

GroupAggregate (cost=142,509.78..142,630.29 rows=1 width=94) (actual time=1,616.690..1,616.749 rows=12 loops=1)

  • Group Key: auolpck_1.orderid
  • Filter: (max((50)) = 10)
  • Rows Removed by Filter: 71
20. 0.189 1,616.699 ↑ 189.6 83 1

Sort (cost=142,509.78..142,549.11 rows=15,735 width=94) (actual time=1,616.686..1,616.699 rows=83 loops=1)

  • Sort Key: auolpck_1.orderid
  • Sort Method: quicksort Memory: 31kB
21. 0.142 1,616.510 ↑ 189.6 83 1

HashAggregate (cost=141,098.21..141,255.56 rows=15,735 width=94) (actual time=1,616.418..1,616.510 rows=83 loops=1)

  • Group Key: auolpck_1.orderid, (50)
22. 0.007 1,616.368 ↑ 189.6 83 1

Append (cost=2,299.05..141,019.54 rows=15,735 width=94) (actual time=1,616.353..1,616.368 rows=83 loops=1)

23. 0.320 1,413.312 ↓ 0.0 0 1

HashAggregate (cost=2,299.05..2,454.40 rows=15,535 width=41) (actual time=1,413.312..1,413.312 rows=0 loops=1)

  • Group Key: auolpck_1.orderid
24. 0.115 1,412.992 ↓ 0.0 0 1

Nested Loop (cost=541.77..2,260.22 rows=15,535 width=37) (actual time=1,412.992..1,412.992 rows=0 loops=1)

25. 0.169 1,407.565 ↑ 2.4 83 1

HashAggregate (cost=541.22..543.22 rows=200 width=90) (actual time=1,407.461..1,407.565 rows=83 loops=1)

  • Group Key: (cte_viable_orders_1.orderid)::text
26. 1,407.396 1,407.396 ↑ 289.8 83 1

CTE Scan on cte_viable_orders cte_viable_orders_1 (cost=0.00..481.08 rows=24,054 width=90) (actual time=1,406.977..1,407.396 rows=83 loops=1)

27. 5.312 5.312 ↓ 0.0 0 83

Index Only Scan using i_audit_olp_orderid_revt_tuid on wms_audit_orderline_picking auolpck_1 (cost=0.56..8.57 rows=1 width=37) (actual time=0.064..0.064 rows=0 loops=83)

  • Index Cond: ((orderid = (cte_viable_orders_1.orderid)::text) AND (revtype = 'OUTBOUND_PICK_ORDER_CONFIRM_INTERFACE_SENT'::text))
  • Heap Fetches: 0
28. 0.045 203.049 ↑ 2.4 83 1

HashAggregate (cost=138,327.11..138,329.11 rows=200 width=94) (actual time=203.039..203.049 rows=83 loops=1)

  • Group Key: auolpck_2.orderid
29. 0.174 203.004 ↑ 199.1 101 1

HashAggregate (cost=137,824.34..138,025.45 rows=20,111 width=94) (actual time=202.915..203.004 rows=101 loops=1)

  • Group Key: auolpck_2.orderid, (10)
30. 0.037 202.830 ↑ 199.1 101 1

Append (cost=134,171.84..137,723.78 rows=20,111 width=94) (actual time=157.801..202.830 rows=101 loops=1)

31. 1.587 159.981 ↑ 242.8 29 1

Group (cost=134,171.84..134,207.04 rows=7,040 width=41) (actual time=157.800..159.981 rows=29 loops=1)

  • Group Key: auolpck_2.orderid
32. 11.820 158.394 ↓ 1.7 11,785 1

Sort (cost=134,171.84..134,189.44 rows=7,040 width=37) (actual time=157.797..158.394 rows=11,785 loops=1)

  • Sort Key: auolpck_2.orderid
  • Sort Method: quicksort Memory: 1305kB
33. 29.210 146.574 ↓ 1.7 11,785 1

Hash Left Join (cost=1,613.48..133,721.93 rows=7,040 width=37) (actual time=26.061..146.574 rows=11,785 loops=1)

  • Hash Cond: ((auolpck_2.orderlineid)::text = (olpck.id)::text)
  • Filter: (olpck.* IS NULL)
  • Rows Removed by Filter: 11765
34. 7.818 92.696 ↑ 59.8 23,550 1

Nested Loop (cost=541.77..92,611.13 rows=1,407,994 width=74) (actual time=0.189..92.696 rows=23,550 loops=1)

35. 0.185 0.218 ↑ 2.4 83 1

HashAggregate (cost=541.22..543.22 rows=200 width=90) (actual time=0.085..0.218 rows=83 loops=1)

  • Group Key: (cte_viable_orders_2.orderid)::text
36. 0.033 0.033 ↑ 289.8 83 1

CTE Scan on cte_viable_orders cte_viable_orders_2 (cost=0.00..481.08 rows=24,054 width=90) (actual time=0.004..0.033 rows=83 loops=1)

37. 84.660 84.660 ↓ 2.4 284 83

Index Scan using i_audit_olp_orderid on wms_audit_orderline_picking auolpck_2 (cost=0.56..459.17 rows=117 width=74) (actual time=0.057..1.020 rows=284 loops=83)

  • Index Cond: ((orderid)::text = (cte_viable_orders_2.orderid)::text)
38. 10.791 24.668 ↑ 1.0 11,454 1

Hash (cost=335.54..335.54 rows=11,454 width=398) (actual time=24.668..24.668 rows=11,454 loops=1)

  • Buckets: 16384 Batches: 2 Memory Usage: 1382kB
39. 13.877 13.877 ↑ 1.0 11,454 1

Seq Scan on wms_orderline_picking olpck (cost=0.00..335.54 rows=11,454 width=398) (actual time=0.033..13.877 rows=11,454 loops=1)

40. 3.152 42.812 ↑ 181.5 72 1

Hash Join (cost=2,347.49..3,215.08 rows=13,071 width=41) (actual time=29.853..42.812 rows=72 loops=1)

  • Hash Cond: ((d.header_id)::text = (cte_viable_orders_3.orderid)::text)
41. 28.988 39.604 ↓ 1.1 28,667 1

HashAggregate (cost=1,801.78..2,193.91 rows=26,142 width=41) (actual time=29.681..39.604 rows=28,667 loops=1)

  • Group Key: d.header_id
42. 10.616 10.616 ↑ 1.0 40,390 1

Seq Scan on wms_orderline d (cost=0.00..1,296.90 rows=40,390 width=41) (actual time=0.054..10.616 rows=40,390 loops=1)

43. 0.015 0.056 ↑ 2.4 83 1

Hash (cost=543.22..543.22 rows=200 width=90) (actual time=0.056..0.056 rows=83 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
44. 0.032 0.041 ↑ 2.4 83 1

HashAggregate (cost=541.22..543.22 rows=200 width=90) (actual time=0.033..0.041 rows=83 loops=1)

  • Group Key: (cte_viable_orders_3.orderid)::text
45. 0.009 0.009 ↑ 289.8 83 1

CTE Scan on cte_viable_orders cte_viable_orders_3 (cost=0.00..481.08 rows=24,054 width=90) (actual time=0.002..0.009 rows=83 loops=1)

46. 0.181 0.192 ↑ 2.4 83 12

HashAggregate (cost=541.22..543.22 rows=200 width=90) (actual time=0.003..0.016 rows=83 loops=12)

  • Group Key: (cte_viable_orders.orderid)::text
47. 0.011 0.011 ↑ 289.8 83 1

CTE Scan on cte_viable_orders (cost=0.00..481.08 rows=24,054 width=90) (actual time=0.002..0.011 rows=83 loops=1)

48. 2.472 2.472 ↓ 3.7 104 12

Index Scan using i_audit_olp_orderid_revt_tuid on wms_audit_orderline_picking auolpck (cost=0.56..155.65 rows=28 width=151) (actual time=0.038..0.206 rows=104 loops=12)

  • Index Cond: (((orderid)::text = (cte_viable_orders.orderid)::text) AND ((revtype)::text = 'PROCESSED'::text))
  • Filter: outbound
49. 15.036 15.036 ↓ 0.0 0 1,253

Index Only Scan using wms_audit_transportunit_revtype_skid_prc on wms_audit_transportunit autu (cost=0.55..1.23 rows=1 width=37) (actual time=0.012..0.012 rows=0 loops=1,253)

  • Index Cond: ((revtype = 'OUTBOUND_PACKING_INTERFACE_SENT'::text) AND (stockkeepableid = (auolpck.transportunitid)::text))
  • Heap Fetches: 332
Planning time : 5.452 ms
Execution time : 1,647.889 ms