explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qr62

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 5,788.665 ↑ 1.0 15 1

Limit (cost=3,976.79..60,727.84 rows=15 width=1,286) (actual time=635.383..5,788.665 rows=15 loops=1)

2. 0.014 5,788.654 ↑ 4,076.7 15 1

Subquery Scan on wms_transportunit_view (cost=3,976.79..231,359,111.13 rows=61,150 width=1,286) (actual time=635.383..5,788.654 rows=15 loops=1)

3. 17.337 5,788.640 ↑ 4,076.7 15 1

Nested Loop Left Join (cost=3,976.79..231,358,499.63 rows=61,150 width=1,298) (actual time=635.382..5,788.640 rows=15 loops=1)

  • Join Filter: ((pt.tu_id)::text = (tu.id)::text)
  • Rows Removed by Join Filter: 183553
4.          

CTE process_tu

5. 25.083 63.098 ↑ 1.0 12,237 1

GroupAggregate (cost=0.55..3,972.45 rows=12,237 width=69) (actual time=0.077..63.098 rows=12,237 loops=1)

  • Group Key: proctu.transportunits_id
6. 12.351 38.015 ↑ 1.0 15,919 1

Nested Loop Left Join (cost=0.55..3,739.89 rows=15,919 width=561) (actual time=0.045..38.015 rows=15,919 loops=1)

7. 9.745 9.745 ↑ 1.0 15,919 1

Index Scan using i_process_tu_tuids on wms_process_tu proctu (cost=0.41..1,315.10 rows=15,919 width=45) (actual time=0.032..9.745 rows=15,919 loops=1)

8. 15.919 15.919 ↑ 1.0 1 15,919

Index Scan using pk_wms_process_id on wms_process process (cost=0.14..0.15 rows=1 width=1,040) (actual time=0.001..0.001 rows=1 loops=15,919)

  • Index Cond: ((proctu.processes_id)::text = (id)::text)
9. 0.033 5,684.258 ↑ 4,076.7 15 1

Nested Loop Left Join (cost=4.34..143,266,632.62 rows=61,150 width=782) (actual time=616.959..5,684.258 rows=15 loops=1)

10. 0.059 5,684.015 ↑ 4,076.7 15 1

Nested Loop Left Join (cost=4.05..143,247,011.64 rows=61,150 width=745) (actual time=616.935..5,684.015 rows=15 loops=1)

11. 2.751 5,683.416 ↑ 4,076.7 15 1

Nested Loop Left Join (cost=3.63..143,184,513.10 rows=61,150 width=731) (actual time=616.919..5,683.416 rows=15 loops=1)

  • Join Filter: ((mcar.id)::text = (sk.parent_id)::text)
  • Rows Removed by Join Filter: 29715
12. 72.174 5,581.515 ↑ 4,076.7 15 1

Nested Loop (cost=2.51..102,164,194.00 rows=61,150 width=705) (actual time=518.924..5,581.515 rows=15 loops=1)

  • Join Filter: ((tu.id)::text = (tu_1.id)::text)
  • Rows Removed by Join Filter: 901303
13. 0.025 2.511 ↑ 4,076.7 15 1

Nested Loop Left Join (cost=2.51..427,779.87 rows=61,150 width=673) (actual time=0.267..2.511 rows=15 loops=1)

14. 0.090 1.931 ↑ 4,076.7 15 1

Nested Loop Left Join (cost=2.09..380,972.28 rows=61,150 width=707) (actual time=0.237..1.931 rows=15 loops=1)

15. 0.026 1.136 ↑ 4,076.7 15 1

Nested Loop Left Join (cost=1.67..334,164.69 rows=61,150 width=741) (actual time=0.142..1.136 rows=15 loops=1)

16. 0.035 1.110 ↑ 4,076.7 15 1

Nested Loop Left Join (cost=1.25..271,666.15 rows=61,150 width=749) (actual time=0.141..1.110 rows=15 loops=1)

17. 0.064 0.970 ↑ 4,076.7 15 1

Nested Loop Left Join (cost=0.84..243,979.27 rows=61,150 width=712) (actual time=0.129..0.970 rows=15 loops=1)

  • Join Filter: ((sk.stockkeepabletype_id)::text = (sk_type.id)::text)
  • Rows Removed by Join Filter: 712
18. 0.066 0.846 ↑ 4,076.7 15 1

Nested Loop (cost=0.84..199,913.00 rows=61,150 width=213) (actual time=0.097..0.846 rows=15 loops=1)

19. 0.150 0.150 ↑ 23,364.8 15 1

Index Scan using uk_stockkeepable_logid_clntid on wms_stockkeepable sk (cost=0.42..41,229.90 rows=350,472 width=86) (actual time=0.049..0.150 rows=15 loops=1)

  • Filter: ((NOT dummy) AND ((stockkeepabletype_id)::text <> 'TT_STOCKTOTE_FAKE_SHUTTLE_SHORTAGE'::text))
20. 0.630 0.630 ↑ 1.0 1 15

Index Scan using pk_transportunit_id on wms_transportunit tu (cost=0.41..0.45 rows=1 width=127) (actual time=0.042..0.042 rows=1 loops=15)

  • Index Cond: ((id)::text = (sk.id)::text)
21. 0.048 0.060 ↑ 1.0 48 15

Materialize (cost=0.00..1.73 rows=49 width=606) (actual time=0.001..0.004 rows=48 loops=15)

22. 0.012 0.012 ↑ 1.0 49 1

Seq Scan on wms_stockkeepabletype sk_type (cost=0.00..1.49 rows=49 width=606) (actual time=0.008..0.012 rows=49 loops=1)

23. 0.105 0.105 ↓ 0.0 0 15

Index Only Scan using pk_transportunit_id on wms_transportunit parent_tu (cost=0.41..0.45 rows=1 width=37) (actual time=0.007..0.007 rows=0 loops=15)

  • Index Cond: (id = (sk.parent_id)::text)
  • Heap Fetches: 0
24. 0.000 0.000 ↓ 0.0 0 15

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable parent_sk (cost=0.42..1.02 rows=1 width=29) (actual time=0.000..0.000 rows=0 loops=15)

  • Index Cond: ((parent_tu.id)::text = (id)::text)
25. 0.705 0.705 ↑ 1.0 1 15

Index Scan using pk_wms_cubinginfo_id on wms_cubinginfo cubing_req (cost=0.42..0.77 rows=1 width=39) (actual time=0.047..0.047 rows=1 loops=15)

  • Index Cond: ((tu.cubinginforequired_id)::text = (id)::text)
26. 0.555 0.555 ↑ 1.0 1 15

Index Scan using pk_wms_cubinginfo_id on wms_cubinginfo cubing_act (cost=0.42..0.77 rows=1 width=39) (actual time=0.037..0.037 rows=1 loops=15)

  • Index Cond: ((tu.cubinginfoactual_id)::text = (id)::text)
27. 151.686 5,506.830 ↑ 1.0 60,088 15

Materialize (cost=0.00..19,166.70 rows=62,228 width=69) (actual time=0.029..367.122 rows=60,088 loops=15)

28. 5,355.144 5,355.144 ↑ 1.0 62,228 1

Seq Scan on wms_transportunit tu_1 (cost=0.00..17,503.28 rows=62,228 width=69) (actual time=0.245..5,355.144 rows=62,228 loops=1)

29. 1.733 99.150 ↑ 1.0 1,981 15

Materialize (cost=1.12..39,203,251.80 rows=1,981 width=89) (actual time=0.003..6.610 rows=1,981 loops=15)

30. 0.000 97.417 ↑ 1.0 1,981 1

Nested Loop Left Join (cost=1.12..39,203,241.89 rows=1,981 width=89) (actual time=0.042..97.417 rows=1,981 loops=1)

31. 18.184 95.655 ↑ 1.0 1,981 1

Merge Left Join (cost=0.69..39,200,841.08 rows=1,981 width=82) (actual time=0.040..95.655 rows=1,981 loops=1)

  • Merge Cond: ((mcar.currentsection_id)::text = (cur_section_location.section_id)::text)
  • Join Filter: ((SubPlan 5) = 1)
  • Rows Removed by Join Filter: 440
32. 0.805 0.805 ↑ 1.0 1,981 1

Index Scan using i_mfc_carrier_cursec_id on mfc_carrier mcar (cost=0.28..209.07 rows=1,981 width=60) (actual time=0.028..0.805 rows=1,981 loops=1)

33. 6.968 26.278 ↓ 1.0 47,111 1

Materialize (cost=0.41..3,659.92 rows=46,922 width=49) (actual time=0.009..26.278 rows=47,111 loops=1)

34. 19.310 19.310 ↑ 1.0 46,922 1

Index Only Scan using pk_mfcsection_loc on mfc_section_wms_location cur_section_location (cost=0.41..3,542.61 rows=46,922 width=49) (actual time=0.007..19.310 rows=46,922 loops=1)

  • Heap Fetches: 46922
35.          

SubPlan (forMerge Left Join)

36. 10.336 50.388 ↑ 1.0 1 646

Aggregate (cost=448.32..448.33 rows=1 width=8) (actual time=0.078..0.078 rows=1 loops=646)

37. 40.052 40.052 ↑ 1.0 300 646

Index Only Scan using pk_mfcsection_loc on mfc_section_wms_location cur_section_location2 (cost=0.41..447.55 rows=309 width=22) (actual time=0.007..0.062 rows=300 loops=646)

  • Index Cond: (section_id = (mcar.currentsection_id)::text)
  • Heap Fetches: 193806
38. 1.981 1.981 ↓ 0.0 0 1,981

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable sk_carrier_location (cost=0.42..1.21 rows=1 width=29) (actual time=0.001..0.001 rows=0 loops=1,981)

  • Index Cond: ((cur_section_location.location_id)::text = (id)::text)
39. 0.540 0.540 ↑ 1.0 1 15

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable sk_parent_location (cost=0.42..1.02 rows=1 width=46) (actual time=0.036..0.036 rows=1 loops=15)

  • Index Cond: (((getlocation(tu_1.id)))::text = (id)::text)
40. 0.210 0.210 ↓ 0.0 0 15

Index Scan using i_insmove_grouptu_id on wms_instruction_movement mov (cost=0.29..0.31 rows=1 width=91) (actual time=0.014..0.014 rows=0 loops=15)

  • Index Cond: ((grouptransportunit_id)::text = (sk.id)::text)
  • Filter: ((NOT stockmove) AND ((type)::text = 'PUT'::text))
41. 82.590 82.590 ↑ 1.0 12,237 15

CTE Scan on process_tu pt (cost=0.00..244.74 rows=12,237 width=122) (actual time=0.006..5.506 rows=12,237 loops=15)

42.          

SubPlan (forNested Loop Left Join)

43. 0.015 0.015 ↓ 0.0 0 15

Index Scan using pk_wms_stockkeepable_id on wms_stockkeepable sk_1 (cost=0.42..8.44 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=15)

  • Index Cond: ((id)::text = (mov.destinationstockkeepable_id)::text)
44. 0.315 0.315 ↓ 0.0 0 15

Seq Scan on wms_subarea sa (cost=0.00..5.14 rows=1 width=18) (actual time=0.020..0.021 rows=0 loops=15)

  • Filter: ((id)::text = (mov.destinationsubarea_id)::text)
  • Rows Removed by Filter: 171
45. 0.060 4.125 ↑ 1.0 1 15

Limit (cost=1,151.10..1,151.11 rows=1 width=12) (actual time=0.274..0.275 rows=1 loops=15)

46. 0.270 4.065 ↑ 286.0 1 15

Sort (cost=1,151.10..1,151.82 rows=286 width=12) (actual time=0.271..0.271 rows=1 loops=15)

  • Sort Key: aot.gendate DESC
  • Sort Method: top-N heapsort Memory: 25kB
47. 3.000 3.795 ↑ 14.3 20 15

Bitmap Heap Scan on wms_audit_orderline_transport aot (cost=19.40..1,149.67 rows=286 width=12) (actual time=0.080..0.253 rows=20 loops=15)

  • Recheck Cond: (((transportunitid)::text = (tu.id)::text) OR ((transportunitid)::text = (parent_sk.id)::text))
  • Heap Blocks: exact=146
48. 0.030 0.795 ↓ 0.0 0 15

BitmapOr (cost=19.40..19.40 rows=286 width=0) (actual time=0.053..0.053 rows=0 loops=15)

49. 0.765 0.765 ↑ 7.2 20 15

Bitmap Index Scan on i_au_ol_transport_tuid (cost=0.00..9.63 rows=143 width=0) (actual time=0.051..0.051 rows=20 loops=15)

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

Bitmap Index Scan on i_au_ol_transport_tuid (cost=0.00..9.63 rows=143 width=0) (actual time=0.000..0.000 rows=0 loops=15)

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