explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VoUf

Settings
# exclusive inclusive rows x rows loops node
1. 9.672 93,768.034 ↓ 16.0 16 1

Nested Loop (cost=20,871.04..20,921.26 rows=1 width=724) (actual time=525.653..93,768.034 rows=16 loops=1)

  • Join Filter: (((row_number() OVER (?)) <= lineqty.lines) AND (inv.kit_id = lineqty.kit_id) AND (inv.item_id = lineqty.item_id) AND ((inv.mfg_lotcode)::text = (lineqty.mfg_lotcode)::text) AND ((inv.mfg_sublotcode)::text = (lineqty.mfg_sublotcode)::text) AND (inv.id = ANY (lineqty.inv_id_array)))
  • Rows Removed by Join Filter: 14,576
2.          

CTE inv

3. 0.000 330.240 ↓ 14,592.0 14,592 1

Nested Loop Anti Join (cost=168.65..20,863.41 rows=1 width=257) (actual time=70.927..330.240 rows=14,592 loops=1)

  • Join Filter: (dv_inventory.warehouse_id = inv_2.warehouse_id)
4. 9.400 170.308 ↓ 14,592.0 14,592 1

Nested Loop (cost=168.08..17,850.53 rows=1 width=257) (actual time=70.896..170.308 rows=14,592 loops=1)

  • Join Filter: ((label.object_id = sie.order_id) AND CASE WHEN (sie.msie_id IS NOT NULL) THEN (sie.msie_id = msie.id) ELSE (label.order_line_id = sie.id) END)
5. 9.260 131.724 ↓ 14,592.0 14,592 1

Nested Loop Left Join (cost=167.65..17,844.06 rows=1 width=269) (actual time=70.875..131.724 rows=14,592 loops=1)

6. 3.639 93.280 ↓ 14,592.0 14,592 1

Nested Loop (cost=167.21..17,840.36 rows=1 width=265) (actual time=70.849..93.280 rows=14,592 loops=1)

7. 0.001 3.977 ↑ 1.0 1 1

Limit (cost=2.57..15,800.91 rows=1 width=4) (actual time=3.977..3.977 rows=1 loops=1)

8. 0.001 3.976 ↑ 1.0 1 1

Nested Loop Anti Join (cost=2.57..15,800.91 rows=1 width=4) (actual time=3.976..3.976 rows=1 loops=1)

  • Join Filter: (dv_inventory_1.warehouse_id = inv_3.warehouse_id)
9. 0.003 3.931 ↑ 1.0 1 1

Nested Loop (cost=2.00..12,788.03 rows=1 width=8) (actual time=3.931..3.931 rows=1 loops=1)

  • Join Filter: ((label_1.object_id = sie_1.order_id) AND CASE WHEN (sie_1.msie_id IS NOT NULL) THEN (sie_1.msie_id = msie_1.id) ELSE (label_1.order_line_id = sie_1.id) END)
10. 0.003 3.915 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.56..12,781.56 rows=1 width=28) (actual time=3.915..3.915 rows=1 loops=1)

11. 0.004 3.896 ↑ 1.0 1 1

Nested Loop (cost=1.13..12,777.86 rows=1 width=24) (actual time=3.896..3.896 rows=1 loops=1)

12. 0.049 0.049 ↑ 12.0 1 1

Index Scan using dv_label_right_idx on dv_label label_1 (cost=0.56..33.92 rows=12 width=8) (actual time=0.049..0.049 rows=1 loops=1)

  • Index Cond: ("right"((item_upc)::text, 18) = '012345670003701032'::text)
13. 3.843 3.843 ↑ 1.0 1 1

Index Scan using dv_inventory_idx3 on dv_inventory inv_3 (cost=0.57..1,061.98 rows=1 width=16) (actual time=3.843..3.843 rows=1 loops=1)

  • Index Cond: (ship_order_id = label_1.object_id)
  • Filter: ((qty_allocated > 0) AND ((COALESCE(pcontainerid, ''::character varying))::text = ''::text) AND (((((qty_available + qty_hold) + qty_quarantine) + qty_damaged) + qty_shipped) = 0))
  • Rows Removed by Filter: 2,944
14. 0.016 0.016 ↑ 1.0 1 1

Index Only Scan using dv_order_mship_item_entry_order_id_id_idx on dv_order_mship_item_entry msie_1 (cost=0.43..3.70 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: ((order_id = label_1.object_id) AND (id = label_1.order_line_id))
  • Heap Fetches: 1
15. 0.013 0.013 ↑ 1.0 1 1

Index Scan using dv_order_ship_item_entry_id_key on dv_order_ship_item_entry sie_1 (cost=0.44..6.46 rows=1 width=12) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (id = inv_3.sie_id)
  • Filter: (inv_3.ship_order_id = order_id)
16. 0.044 0.044 ↓ 0.0 0 1

Index Scan using dv_inventory_right_idx on dv_inventory dv_inventory_1 (cost=0.57..3,012.85 rows=364 width=4) (actual time=0.044..0.044 rows=0 loops=1)

  • Index Cond: ("right"((pcontainerid)::text, 18) = '012345670003701032'::text)
  • Filter: ((qty_allocated + qty_shipped) > 0)
17. 5.195 85.664 ↓ 14,592.0 14,592 1

Nested Loop (cost=164.65..2,039.42 rows=1 width=265) (actual time=66.870..85.664 rows=14,592 loops=1)

18. 0.015 0.015 ↑ 12.0 1 1

Index Scan using dv_label_right_idx on dv_label label (cost=0.56..33.92 rows=12 width=28) (actual time=0.013..0.015 rows=1 loops=1)

  • Index Cond: ("right"((item_upc)::text, 18) = '012345670003701032'::text)
19. 14.811 80.454 ↓ 14,592.0 14,592 1

Bitmap Heap Scan on dv_inventory inv_2 (cost=164.08..167.12 rows=1 width=237) (actual time=66.852..80.454 rows=14,592 loops=1)

  • Recheck Cond: ((ship_order_id = label.object_id) AND (storage_id = inv_3.storage_id))
  • Filter: ((qty_allocated > 0) AND ((COALESCE(pcontainerid, ''::character varying))::text = ''::text) AND (((((qty_available + qty_hold) + qty_quarantine) + qty_damaged) + qty_shipped) = 0))
  • Rows Removed by Filter: 13,568
  • Heap Blocks: exact=901
20. 0.066 65.643 ↓ 0.0 0 1

BitmapAnd (cost=164.08..164.08 rows=1 width=0) (actual time=65.643..65.643 rows=0 loops=1)

21. 2.174 2.174 ↓ 12.8 31,504 1

Bitmap Index Scan on dv_inventory_idx3 (cost=0.00..43.08 rows=2,468 width=0) (actual time=2.174..2.174 rows=31,504 loops=1)

  • Index Cond: (ship_order_id = label.object_id)
22. 63.403 63.403 ↓ 212.3 1,448,711 1

Bitmap Index Scan on dv_inventory_idx5 (cost=0.00..120.75 rows=6,825 width=0) (actual time=63.403..63.403 rows=1,448,711 loops=1)

  • Index Cond: (storage_id = inv_3.storage_id)
23. 29.184 29.184 ↑ 1.0 1 14,592

Index Only Scan using dv_order_mship_item_entry_order_id_id_idx on dv_order_mship_item_entry msie (cost=0.43..3.70 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=14,592)

  • Index Cond: ((order_id = label.object_id) AND (id = label.order_line_id))
  • Heap Fetches: 14,592
24. 29.184 29.184 ↑ 1.0 1 14,592

Index Scan using dv_order_ship_item_entry_id_key on dv_order_ship_item_entry sie (cost=0.44..6.46 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=14,592)

  • Index Cond: (id = inv_2.sie_id)
  • Filter: (inv_2.ship_order_id = order_id)
25. 160.512 160.512 ↓ 0.0 0 14,592

Index Scan using dv_inventory_right_idx on dv_inventory (cost=0.57..3,012.85 rows=364 width=4) (actual time=0.011..0.011 rows=0 loops=14,592)

  • Index Cond: ("right"((pcontainerid)::text, 18) = '012345670003701032'::text)
  • Filter: ((qty_allocated + qty_shipped) > 0)
26.          

CTE lineqty

27. 5.006 127.253 ↑ 1.0 1 1

GroupAggregate (cost=7.49..7.53 rows=1 width=140) (actual time=127.253..127.253 rows=1 loops=1)

  • Group Key: kit.id, inv_4.kit_id, inv_4.item_id, inv_4.mfg_lotcode, inv_4.mfg_sublotcode, uom.ip_case_qty, kit.qty
28. 9.460 122.247 ↓ 14,592.0 14,592 1

Sort (cost=7.49..7.49 rows=1 width=108) (actual time=121.408..122.247 rows=14,592 loops=1)

  • Sort Key: kit.id, inv_4.kit_id, inv_4.item_id, inv_4.mfg_lotcode, inv_4.mfg_sublotcode, uom.ip_case_qty, kit.qty
  • Sort Method: quicksort Memory: 1,525kB
29. 4.148 112.787 ↓ 14,592.0 14,592 1

Nested Loop (cost=1.29..7.48 rows=1 width=108) (actual time=0.069..112.787 rows=14,592 loops=1)

30. 4.131 79.455 ↓ 14,592.0 14,592 1

Nested Loop (cost=0.86..7.02 rows=1 width=108) (actual time=0.048..79.455 rows=14,592 loops=1)

31. 14.047 46.140 ↓ 14,592.0 14,592 1

Nested Loop (cost=0.43..6.56 rows=1 width=108) (actual time=0.025..46.140 rows=14,592 loops=1)

32. 2.909 2.909 ↓ 14,592.0 14,592 1

CTE Scan on inv inv_4 (cost=0.00..0.02 rows=1 width=96) (actual time=0.001..2.909 rows=14,592 loops=1)

33. 29.184 29.184 ↑ 1.0 1 14,592

Index Scan using dv_kit_subitems_idx1 on dv_kit_subitems kit (cost=0.43..6.53 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=14,592)

  • Index Cond: (parent_item_id = inv_4.kit_id)
  • Filter: ((inv_4.item_id = child_item_id) AND ((COALESCE(inv_4.mfg_lotcode, ''::character varying))::text = (COALESCE(mfg_lotcode, ''::character varying))::text) AND ((COALESCE(inv_4.mfg_sublotcode, ''::character varying))::text = (COALESCE(mfg_sublotcode, ''::character varying))::text))
34. 29.184 29.184 ↑ 1.0 1 14,592

Index Only Scan using dv_item_id_uom_id_idx on dv_item item (cost=0.43..0.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=14,592)

  • Index Cond: (id = kit.parent_item_id)
  • Heap Fetches: 14,592
35. 29.184 29.184 ↑ 1.0 1 14,592

Index Only Scan using dv_uom_id_item_id_ip_case_qty_ea_case_qty_idx on dv_uom uom (cost=0.43..0.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=14,592)

  • Index Cond: (id = item.uom_id)
  • Heap Fetches: 14,592
36. 81,592.115 93,699.994 ↓ 14,592.0 14,592 1

Merge Join (cost=0.10..50.26 rows=1 width=812) (actual time=525.612..93,699.994 rows=14,592 loops=1)

  • Merge Cond: (inv_1.item_id = inv.item_id)
  • Join Filter: ((inv.kit_id = inv_1.kit_id) AND ((inv.mfg_lotcode)::text = (inv_1.mfg_lotcode)::text) AND ((inv.mfg_sublotcode)::text = (inv_1.mfg_sublotcode)::text) AND (inv.id = inv_1.id))
  • Rows Removed by Join Filter: 212,911,872
37. 19.330 523.206 ↓ 14.6 14,592 1

WindowAgg (cost=0.07..37.60 rows=1,000 width=104) (actual time=508.748..523.206 rows=14,592 loops=1)

38. 2.727 503.876 ↓ 14.6 14,592 1

Nested Loop (cost=0.07..20.10 rows=1,000 width=96) (actual time=477.914..503.876 rows=14,592 loops=1)

39. 6.684 486.557 ↓ 14,592.0 14,592 1

Merge Join (cost=0.06..0.10 rows=1 width=104) (actual time=477.903..486.557 rows=14,592 loops=1)

  • Merge Cond: ((inv_1.item_id = lineqty_1.item_id) AND (inv_1.kit_id = lineqty_1.kit_id))
  • Join Filter: ((NOT ((inv_1.mfg_lotcode)::text IS DISTINCT FROM (lineqty_1.mfg_lotcode)::text)) AND (NOT ((inv_1.mfg_sublotcode)::text IS DISTINCT FROM (lineqty_1.mfg_sublotcode)::text)))
40. 3.766 351.527 ↓ 14,592.0 14,592 1

Sort (cost=0.03..0.04 rows=1 width=100) (actual time=350.598..351.527 rows=14,592 loops=1)

  • Sort Key: inv_1.item_id, inv_1.kit_id
  • Sort Method: quicksort Memory: 1,525kB
41. 347.761 347.761 ↓ 14,592.0 14,592 1

CTE Scan on inv inv_1 (cost=0.00..0.02 rows=1 width=100) (actual time=70.935..347.761 rows=14,592 loops=1)

42. 1.069 128.346 ↑ 1.0 1 1

Sort (cost=0.03..0.04 rows=1 width=96) (actual time=127.295..128.346 rows=1 loops=1)

  • Sort Key: lineqty_1.item_id, lineqty_1.kit_id
  • Sort Method: quicksort Memory: 25kB
43. 127.277 127.277 ↑ 1.0 1 1

CTE Scan on lineqty lineqty_1 (cost=0.00..0.02 rows=1 width=96) (actual time=127.276..127.277 rows=1 loops=1)

44. 14.592 14.592 ↑ 1,000.0 1 14,592

Function Scan on generate_series (cost=0.01..10.01 rows=1,000 width=0) (actual time=0.001..0.001 rows=1 loops=14,592)

45. 11,565.753 11,584.673 ↓ 212,911,873.0 212,911,873 1

Materialize (cost=0.03..0.04 rows=1 width=712) (actual time=16.853..11,584.673 rows=212,911,873 loops=1)

46. 12.401 18.920 ↓ 14,592.0 14,592 1

Sort (cost=0.03..0.04 rows=1 width=712) (actual time=16.851..18.920 rows=14,592 loops=1)

  • Sort Key: inv.item_id
  • Sort Method: external merge Disk: 2,608kB
47. 6.519 6.519 ↓ 14,592.0 14,592 1

CTE Scan on inv (cost=0.00..0.02 rows=1 width=712) (actual time=0.003..6.519 rows=14,592 loops=1)

48. 58.368 58.368 ↑ 1.0 1 14,592

CTE Scan on lineqty (cost=0.00..0.02 rows=1 width=128) (actual time=0.004..0.004 rows=1 loops=14,592)

Planning time : 13.132 ms
Execution time : 93,770.196 ms