explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xMtK

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 11.716 ↑ 1.0 9 1

WindowAgg (cost=2,020.69..2,165.54 rows=9 width=789) (actual time=11.713..11.716 rows=9 loops=1)

2. 0.067 11.673 ↑ 1.0 9 1

Result (cost=2,020.69..2,165.34 rows=9 width=793) (actual time=11.413..11.673 rows=9 loops=1)

3.          

CTE process_tu

4. 0.018 0.075 ↑ 1.8 5 1

GroupAggregate (cost=2.70..2.88 rows=9 width=69) (actual time=0.068..0.075 rows=5 loops=1)

  • Group Key: proctu.transportunits_id
5. 0.026 0.057 ↑ 1.8 5 1

Sort (cost=2.70..2.72 rows=9 width=561) (actual time=0.056..0.057 rows=5 loops=1)

  • Sort Key: proctu.transportunits_id
  • Sort Method: quicksort Memory: 25kB
6. 0.006 0.031 ↑ 1.8 5 1

Hash Left Join (cost=1.36..2.55 rows=9 width=561) (actual time=0.031..0.031 rows=5 loops=1)

  • Hash Cond: ((proctu.processes_id)::text = (process.id)::text)
7. 0.008 0.008 ↑ 1.8 5 1

Seq Scan on wms_process_tu proctu (cost=0.00..1.09 rows=9 width=45) (actual time=0.008..0.008 rows=5 loops=1)

8. 0.007 0.017 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=1,040) (actual time=0.017..0.017 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.010 0.010 ↑ 1.0 16 1

Seq Scan on wms_process process (cost=0.00..1.16 rows=16 width=1,040) (actual time=0.008..0.010 rows=16 loops=1)

10. 0.034 11.252 ↑ 1.0 9 1

Sort (cost=2,017.81..2,017.83 rows=9 width=275) (actual time=11.252..11.252 rows=9 loops=1)

  • Sort Key: sk.logical_id
  • Sort Method: quicksort Memory: 28kB
11. 0.015 11.218 ↑ 1.0 9 1

Nested Loop Left Join (cost=10.05..2,017.67 rows=9 width=275) (actual time=9.723..11.218 rows=9 loops=1)

  • Join Filter: ((pt.tu_id)::text = (tu.id)::text)
  • Rows Removed by Join Filter: 40
12. 0.021 11.122 ↑ 1.0 9 1

Nested Loop Left Join (cost=10.05..2,015.73 rows=9 width=282) (actual time=9.646..11.122 rows=9 loops=1)

  • Join Filter: ((mov.grouptransportunit_id)::text = (sk.id)::text)
  • Rows Removed by Join Filter: 40
13. 0.004 11.065 ↑ 1.0 9 1

Nested Loop Left Join (cost=10.05..2,013.08 rows=9 width=240) (actual time=9.616..11.065 rows=9 loops=1)

14. 0.026 11.007 ↑ 1.0 9 1

Nested Loop Left Join (cost=9.63..1,991.12 rows=9 width=232) (actual time=9.611..11.007 rows=9 loops=1)

  • Join Filter: ((mcar.id)::text = (sk.parent_id)::text)
  • Rows Removed by Join Filter: 40
15. 0.001 1.819 ↑ 1.0 9 1

Nested Loop Left Join (cost=8.05..57.14 rows=9 width=195) (actual time=0.435..1.819 rows=9 loops=1)

16. 0.008 1.809 ↑ 1.0 9 1

Nested Loop Left Join (cost=7.63..35.18 rows=9 width=208) (actual time=0.430..1.809 rows=9 loops=1)

17. 0.007 1.783 ↑ 1.0 9 1

Nested Loop Left Join (cost=7.49..33.80 rows=9 width=171) (actual time=0.422..1.783 rows=9 loops=1)

18. 0.016 1.713 ↑ 1.0 9 1

Hash Join (cost=7.35..32.38 rows=9 width=171) (actual time=0.384..1.713 rows=9 loops=1)

  • Hash Cond: ((sk.id)::text = (tu.id)::text)
19. 0.011 1.609 ↑ 1.0 9 1

Nested Loop (cost=0.42..25.41 rows=9 width=149) (actual time=0.290..1.609 rows=9 loops=1)

20. 1.544 1.544 ↑ 1.0 9 1

Seq Scan on wms_transportunit tu_1 (cost=0.00..3.34 rows=9 width=69) (actual time=0.282..1.544 rows=9 loops=1)

21. 0.054 0.054 ↑ 1.0 1 9

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable sk (cost=0.42..2.44 rows=1 width=80) (actual time=0.006..0.006 rows=1 loops=9)

  • Index Cond: ((id)::text = (tu_1.id)::text)
  • Filter: ((NOT dummy) AND ((stockkeepabletype_id)::text <> 'TT_STOCKTOTE_FAKE_SHUTTLE_SHORTAGE'::text))
22. 0.005 0.088 ↑ 1.0 9 1

Hash (cost=6.82..6.82 rows=9 width=59) (actual time=0.088..0.088 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.012 0.083 ↑ 1.0 9 1

Hash Right Join (cost=4.07..6.82 rows=9 width=59) (actual time=0.076..0.083 rows=9 loops=1)

  • Hash Cond: ((cubing_act.id)::text = (tu.cubinginfoactual_id)::text)
24. 0.009 0.009 ↑ 1.0 48 1

Seq Scan on wms_cubinginfo cubing_act (cost=0.00..2.48 rows=48 width=25) (actual time=0.006..0.009 rows=48 loops=1)

25. 0.022 0.062 ↑ 1.0 9 1

Hash (cost=3.95..3.95 rows=9 width=93) (actual time=0.062..0.062 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
26. 0.012 0.040 ↑ 1.0 9 1

Hash Right Join (cost=1.20..3.95 rows=9 width=93) (actual time=0.032..0.040 rows=9 loops=1)

  • Hash Cond: ((cubing_req.id)::text = (tu.cubinginforequired_id)::text)
27. 0.012 0.012 ↑ 1.0 48 1

Seq Scan on wms_cubinginfo cubing_req (cost=0.00..2.48 rows=48 width=25) (actual time=0.008..0.012 rows=48 loops=1)

28. 0.007 0.016 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=127) (actual time=0.016..0.016 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
29. 0.009 0.009 ↑ 1.0 9 1

Seq Scan on wms_transportunit tu (cost=0.00..1.09 rows=9 width=127) (actual time=0.009..0.009 rows=9 loops=1)

30. 0.063 0.063 ↑ 1.0 1 9

Index Scan using pk_wms_stockkeepabletype_id on wms_stockkeepabletype sk_type (cost=0.14..0.16 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=9)

  • Index Cond: ((sk.stockkeepabletype_id)::text = (id)::text)
31. 0.018 0.018 ↓ 0.0 0 9

Index Only Scan using pk_transportunit_id on wms_transportunit parent_tu (cost=0.14..0.15 rows=1 width=37) (actual time=0.002..0.002 rows=0 loops=9)

  • Index Cond: (id = (sk.parent_id)::text)
  • Heap Fetches: 0
32. 0.009 0.009 ↓ 0.0 0 9

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable parent_sk (cost=0.42..2.44 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=9)

  • Index Cond: ((parent_tu.id)::text = (id)::text)
33. 0.011 9.162 ↑ 1.4 5 9

Materialize (cost=1.58..1,933.06 rows=7 width=100) (actual time=1.017..1.018 rows=5 loops=9)

34. 0.009 9.151 ↑ 1.4 5 1

Nested Loop Left Join (cost=1.58..1,933.02 rows=7 width=100) (actual time=9.148..9.151 rows=5 loops=1)

35. 4.854 9.127 ↑ 1.4 5 1

Hash Right Join (cost=1.16..1,928.96 rows=7 width=98) (actual time=9.124..9.127 rows=5 loops=1)

  • Hash Cond: ((cur_section_location.section_id)::text = (mcar.currentsection_id)::text)
  • Join Filter: ((SubPlan 5) = 1)
36. 4.261 4.261 ↑ 1.0 46,922 1

Seq Scan on mfc_section_wms_location cur_section_location (cost=0.00..974.16 rows=48,616 width=49) (actual time=0.030..4.261 rows=46,922 loops=1)

37. 0.004 0.012 ↑ 1.4 5 1

Hash (cost=1.07..1.07 rows=7 width=76) (actual time=0.012..0.012 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.008 0.008 ↑ 1.4 5 1

Seq Scan on mfc_carrier mcar (cost=0.00..1.07 rows=7 width=76) (actual time=0.007..0.008 rows=5 loops=1)

39.          

SubPlan (forHash Right Join)

40. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=101.49..101.50 rows=1 width=8) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_mfcsection_loc on mfc_section_wms_location cur_section_location2 (cost=0.41..100.86 rows=251 width=22) (never executed)

  • Index Cond: (section_id = (mcar.currentsection_id)::text)
  • Heap Fetches: 0
42. 0.015 0.015 ↓ 0.0 0 5

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable sk_carrier_location (cost=0.42..0.58 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=5)

  • Index Cond: ((cur_section_location.location_id)::text = (id)::text)
43. 0.054 0.054 ↓ 0.0 0 9

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable sk_parent_location (cost=0.42..2.44 rows=1 width=40) (actual time=0.006..0.006 rows=0 loops=9)

  • Index Cond: (((getlocation(tu_1.id)))::text = (id)::text)
44. 0.011 0.036 ↑ 2.2 5 9

Materialize (cost=0.00..1.19 rows=11 width=91) (actual time=0.003..0.004 rows=5 loops=9)

45. 0.025 0.025 ↑ 2.2 5 1

Seq Scan on wms_instruction_movement mov (cost=0.00..1.14 rows=11 width=91) (actual time=0.022..0.025 rows=5 loops=1)

  • Filter: ((NOT stockmove) AND ((type)::text = 'PUT'::text))
46. 0.081 0.081 ↑ 1.8 5 9

CTE Scan on process_tu pt (cost=0.00..0.18 rows=9 width=122) (actual time=0.008..0.009 rows=5 loops=9)

47.          

SubPlan (forResult)

48. 0.018 0.018 ↓ 0.0 0 9

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable sk_1 (cost=0.42..2.44 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=9)

  • Index Cond: ((id)::text = (mov.destinationstockkeepable_id)::text)
49. 0.021 0.021 ↓ 0.0 0 7

Index Scan using pk_wms_subarea_id on wms_subarea sa (cost=0.27..2.29 rows=1 width=18) (actual time=0.003..0.003 rows=0 loops=7)

  • Index Cond: ((id)::text = (mov.destinationsubarea_id)::text)
50. 0.009 0.315 ↑ 1.0 1 9

Limit (cost=10.83..10.83 rows=1 width=12) (actual time=0.035..0.035 rows=1 loops=9)

51. 0.126 0.306 ↑ 9.0 1 9

Sort (cost=10.83..10.85 rows=9 width=12) (actual time=0.034..0.034 rows=1 loops=9)

  • Sort Key: aot.gendate DESC
  • Sort Method: top-N heapsort Memory: 25kB
52. 0.072 0.180 ↑ 2.2 4 9

Bitmap Heap Scan on wms_audit_orderline_transport aot (cost=2.62..10.78 rows=9 width=12) (actual time=0.016..0.020 rows=4 loops=9)

  • Recheck Cond: (((transportunitid)::text = (tu.id)::text) OR ((transportunitid)::text = (parent_sk.id)::text))
  • Heap Blocks: exact=16
53. 0.009 0.108 ↓ 0.0 0 9

BitmapOr (cost=2.62..2.62 rows=9 width=0) (actual time=0.012..0.012 rows=0 loops=9)

54. 0.099 0.099 ↑ 1.2 4 9

Bitmap Index Scan on i_au_ol_transport_tuid (cost=0.00..1.31 rows=5 width=0) (actual time=0.011..0.011 rows=4 loops=9)

  • Index Cond: ((transportunitid)::text = (tu.id)::text)
55. 0.000 0.000 ↓ 0.0 0 9

Bitmap Index Scan on i_au_ol_transport_tuid (cost=0.00..1.31 rows=5 width=0) (actual time=0.000..0.000 rows=0 loops=9)

  • Index Cond: ((transportunitid)::text = (parent_sk.id)::text)