explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oLyT

Settings
# exclusive inclusive rows x rows loops node
1. 0.961 1,971,086.266 ↓ 6.6 191 1

Sort (cost=1,366,759.17..1,366,759.24 rows=29 width=3,606) (actual time=1,971,086.253..1,971,086.266 rows=191 loops=1)

  • Sort Key: a.vendor_po_no
  • Sort Method: quicksort Memory: 276kB
2. 130.668 1,971,085.305 ↓ 6.6 191 1

Hash Right Join (cost=1,336,390.48..1,366,758.47 rows=29 width=3,606) (actual time=1,967,359.501..1,971,085.305 rows=191 loops=1)

  • Hash Cond: ((item_sku.item_sid = a.item_sid) AND ((item_sku.color_sid)::bpchar = a.specification_color_sid))
3. 2,342.975 9,994.355 ↓ 2.6 518,024 1

GroupAggregate (cost=504,066.87..531,005.82 rows=195,920 width=98) (actual time=6,388.177..9,994.355 rows=518,024 loops=1)

  • Group Key: item_sku.item_sid, item_sku.color_sid
4. 6,646.591 7,651.380 ↓ 1.0 1,965,249 1

Sort (cost=504,066.87..508,964.86 rows=1,959,196 width=82) (actual time=6,388.153..7,651.380 rows=1,965,249 loops=1)

  • Sort Key: item_sku.item_sid, item_sku.color_sid
  • Sort Method: external merge Disk: 175768kB
5. 1,004.789 1,004.789 ↓ 1.0 1,965,249 1

Seq Scan on item_sku (cost=0.00..111,810.96 rows=1,959,196 width=82) (actual time=0.026..1,004.789 rows=1,965,249 loops=1)

6. 0.743 1,960,960.282 ↓ 31.8 191 1

Hash (cost=832,323.52..832,323.52 rows=6 width=3,670) (actual time=1,960,960.282..1,960,960.282 rows=191 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 204kB
7. 0.151 1,960,959.539 ↓ 31.8 191 1

Subquery Scan on a (cost=832,317.54..832,323.52 rows=6 width=3,670) (actual time=1,960,916.037..1,960,959.539 rows=191 loops=1)

8. 43.453 1,960,959.388 ↓ 31.8 191 1

GroupAggregate (cost=832,317.54..832,323.46 rows=6 width=3,736) (actual time=1,960,916.035..1,960,959.388 rows=191 loops=1)

  • Group Key: cpmdoc.doc_sid, vpo.vendor_po_no, vpoic_1.vendor_po_item_sid
9. 5.270 1,960,915.935 ↓ 286.5 1,719 1

Sort (cost=832,317.54..832,317.55 rows=6 width=574) (actual time=1,960,915.620..1,960,915.935 rows=1,719 loops=1)

  • Sort Key: cpmdoc.doc_sid, vpo.vendor_po_no, vpoic_1.vendor_po_item_sid
  • Sort Method: quicksort Memory: 1617kB
10. 1.915 1,960,910.665 ↓ 286.5 1,719 1

Nested Loop (cost=555,380.06..832,317.46 rows=6 width=574) (actual time=20,416.072..1,960,910.665 rows=1,719 loops=1)

11. 0.721 1,960,371.658 ↓ 191.0 191 1

Nested Loop (cost=555,379.50..822,939.72 rows=1 width=527) (actual time=20,408.398..1,960,371.658 rows=191 loops=1)

12. 0.949 1,960,354.702 ↓ 191.0 191 1

Nested Loop Left Join (cost=555,379.09..822,933.75 rows=1 width=560) (actual time=20,408.383..1,960,354.702 rows=191 loops=1)

13. 3.484 1,960,314.980 ↓ 191.0 191 1

Nested Loop (cost=555,378.68..822,928.52 rows=1 width=554) (actual time=20,408.361..1,960,314.980 rows=191 loops=1)

14. 13,134.455 1,959,924.912 ↓ 191.0 191 1

Nested Loop Left Join (cost=555,378.26..822,923.24 rows=1 width=430) (actual time=20,403.669..1,959,924.912 rows=191 loops=1)

  • Join Filter: (vpoic_1.vendor_po_item_sid = vpoitem.sid)
  • Rows Removed by Join Filter: 79091102
15. 296.483 12,410.644 ↓ 191.0 191 1

Hash Right Join (cost=222,361.93..414,200.82 rows=1 width=398) (actual time=9,522.981..12,410.644 rows=191 loops=1)

  • Hash Cond: ((vpoic.vendor_po_item_sid = vpoitem.sid) AND ((vposi.vendor_po_shipment_sid)::bpchar = vpoship.sid))
16. 2,651.114 10,103.763 ↑ 1.0 1,189,529 1

Hash Join (cost=164,062.65..346,933.05 rows=1,195,797 width=107) (actual time=4,470.874..10,103.763 rows=1,189,529 loops=1)

  • Hash Cond: ((vposic.vendor_po_item_color_sid)::bpchar = vpoic.sid)
17. 1,941.094 4,670.456 ↑ 1.0 1,190,459 1

Hash Join (cost=56,705.06..178,537.54 rows=1,196,156 width=99) (actual time=1,680.811..4,670.456 rows=1,190,459 loops=1)

  • Hash Cond: (vposic.vendor_po_shipment_d_sid = vposi.sid)
18. 1,049.583 1,049.583 ↑ 1.0 1,190,459 1

Seq Scan on vendor_po_shipment_d_c vposic (cost=0.00..71,405.56 rows=1,196,156 width=99) (actual time=0.012..1,049.583 rows=1,190,459 loops=1)

19. 643.052 1,679.779 ↓ 1.0 850,354 1

Hash (cost=36,231.36..36,231.36 rows=845,336 width=66) (actual time=1,679.779..1,679.779 rows=850,354 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 3066kB
20. 1,036.727 1,036.727 ↓ 1.0 850,354 1

Seq Scan on vendor_po_shipment_d vposi (cost=0.00..36,231.36 rows=845,336 width=66) (actual time=0.011..1,036.727 rows=850,354 loops=1)

21. 1,452.602 2,782.193 ↓ 1.0 1,626,373 1

Hash (cost=66,638.15..66,638.15 rows=1,616,115 width=74) (actual time=2,782.193..2,782.193 rows=1,626,373 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 3160kB
22. 1,329.591 1,329.591 ↓ 1.0 1,626,373 1

Seq Scan on vendor_po_item_color vpoic (cost=0.00..66,638.15 rows=1,616,115 width=74) (actual time=0.017..1,329.591 rows=1,626,373 loops=1)

23. 0.512 2,010.398 ↓ 191.0 191 1

Hash (cost=58,299.27..58,299.27 rows=1 width=390) (actual time=2,010.398..2,010.398 rows=191 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 91kB
24. 0.483 2,009.886 ↓ 191.0 191 1

Nested Loop (cost=2,110.04..58,299.27 rows=1 width=390) (actual time=143.933..2,009.886 rows=191 loops=1)

25. 0.492 1,878.950 ↓ 191.0 191 1

Nested Loop Left Join (cost=2,109.62..58,293.96 rows=1 width=305) (actual time=142.972..1,878.950 rows=191 loops=1)

26. 0.425 1,826.315 ↓ 191.0 191 1

Nested Loop (cost=2,109.19..58,288.68 rows=1 width=272) (actual time=142.022..1,826.315 rows=191 loops=1)

27. 0.366 1,715.874 ↓ 191.0 191 1

Nested Loop (cost=2,108.77..58,283.19 rows=1 width=234) (actual time=141.314..1,715.874 rows=191 loops=1)

28. 0.269 1,713.789 ↓ 191.0 191 1

Nested Loop Left Join (cost=2,108.36..58,277.23 rows=1 width=252) (actual time=140.958..1,713.789 rows=191 loops=1)

29. 0.000 1,710.082 ↓ 191.0 191 1

Gather (cost=2,107.94..58,271.37 rows=1 width=272) (actual time=140.639..1,710.082 rows=191 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
30. 17.989 1,842.759 ↓ 64.0 64 3

Nested Loop (cost=1,107.94..57,271.27 rows=1 width=272) (actual time=110.160..1,842.759 rows=64 loops=3)

31. 1.058 1,823.602 ↓ 16.0 16 3

Hash Join (cost=1,107.52..57,241.86 rows=1 width=206) (actual time=109.105..1,823.602 rows=16 loops=3)

  • Hash Cond: ((vpo.team)::bpchar = hcl4.sid)
32. 1,763.576 1,763.576 ↓ 12.1 3,299 3

Parallel Seq Scan on vendor_po vpo (cost=0.00..56,133.63 rows=272 width=224) (actual time=2.191..1,763.576 rows=3,299 loops=3)

  • Filter: (is_latest AND ((doc_status)::text <> 'inactive'::text) AND (lower((year)::text) = '2019'::text))
  • Rows Removed by Filter: 122892
33. 0.012 58.968 ↑ 50.0 2 3

Hash (cost=1,106.27..1,106.27 rows=100 width=48) (actual time=58.968..58.968 rows=2 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 58.956 58.956 ↑ 50.0 2 3

Seq Scan on hcl_entry hcl4 (cost=0.00..1,106.27 rows=100 width=48) (actual time=14.918..58.956 rows=2 loops=3)

  • Filter: (lower((name)::text) = 'toys team'::text)
  • Rows Removed by Filter: 20379
35. 1.168 1.168 ↓ 4.0 4 49

Index Scan using cpm_document_doc_sid_idx on cpm_document cpmdoc (cost=0.42..29.40 rows=1 width=99) (actual time=1.022..1.168 rows=4 loops=49)

  • Index Cond: (doc_sid = vpo.sid)
  • Filter: ((NOT is_deleted) AND is_latest AND ((ref_entity_name)::text = 'VpoShipDtl'::text))
36. 3.438 3.438 ↑ 1.0 1 191

Index Scan using hcl_entry_pk on hcl_entry hcl1 (cost=0.41..5.85 rows=1 width=46) (actual time=0.018..0.018 rows=1 loops=191)

  • Index Cond: (sid = (vpo.merchandise_hierarchy)::bpchar)
37. 1.719 1.719 ↑ 1.0 1 191

Index Scan using hcl_entry_pk on hcl_entry hcl3 (cost=0.41..5.86 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=191)

  • Index Cond: (sid = (vpo.sourcing_office)::bpchar)
  • Filter: ((name IS NOT NULL) AND ((name)::text <> 'Turkey'::text) AND (lower((name)::text) = 'hong kong'::text))
38. 110.016 110.016 ↑ 1.0 1 191

Index Scan using vendor_po_shipment_d_pk on vendor_po_shipment_d vposhipd (cost=0.42..5.49 rows=1 width=104) (actual time=0.576..0.576 rows=1 loops=191)

  • Index Cond: (sid = cpmdoc.ref_doc_sid)
39. 52.143 52.143 ↑ 1.0 1 191

Index Scan using vendor_po_shipment_pk on vendor_po_shipment vpoship (cost=0.42..5.28 rows=1 width=66) (actual time=0.273..0.273 rows=1 loops=191)

  • Index Cond: (sid = (vposhipd.vendor_po_shipment_sid)::bpchar)
40. 130.453 130.453 ↑ 1.0 1 191

Index Scan using vendor_po_item_pk on vendor_po_item vpoitem (cost=0.42..5.31 rows=1 width=118) (actual time=0.683..0.683 rows=1 loops=191)

  • Index Cond: (sid = (vposhipd.vendor_po_item_sid)::bpchar)
41. 114,689.770 1,934,379.813 ↓ 1.7 414,091 191

Finalize GroupAggregate (cost=333,016.32..403,202.35 rows=245,336 width=65) (actual time=7,971.726..10,127.643 rows=414,091 loops=191)

  • Group Key: vpoic_1.vendor_po_item_sid
42. 0.000 1,819,690.043 ↑ 1.1 442,946 191

Gather Merge (cost=333,016.32..396,455.61 rows=490,672 width=65) (actual time=7,971.594..9,527.173 rows=442,946 loops=191)

  • Workers Planned: 2
  • Workers Launched: 2
43. 123,155.463 4,225,367.895 ↑ 1.7 148,055 573

Partial GroupAggregate (cost=332,016.30..338,819.87 rows=245,336 width=65) (actual time=6,985.145..7,374.115 rows=148,055 loops=573)

  • Group Key: vpoic_1.vendor_po_item_sid
44. 1,309,041.993 4,102,212.432 ↑ 2.5 198,831 573

Sort (cost=332,016.30..333,261.92 rows=498,249 width=37) (actual time=6,983.282..7,159.184 rows=198,831 loops=573)

  • Sort Key: vpoic_1.vendor_po_item_sid
  • Sort Method: external sort Disk: 24032kB
45. 533,549.523 2,793,170.439 ↑ 1.3 396,510 573

Hash Join (cost=159,181.65..271,240.23 rows=498,249 width=37) (actual time=2,790.635..4,874.643 rows=396,510 loops=573)

  • Hash Cond: ((vposic_1.vendor_po_item_color_sid)::bpchar = vpoic_1.sid)
46. 433,903.677 1,157,447.394 ↑ 1.3 396,820 573

Hash Join (cost=53,403.06..137,426.35 rows=498,398 width=37) (actual time=861.732..2,019.978 rows=396,820 loops=573)

  • Hash Cond: (vposic_1.vendor_po_shipment_d_sid = vposi_1.sid)
47. 230,447.421 230,447.421 ↑ 1.3 396,820 573

Parallel Seq Scan on vendor_po_shipment_d_c vposic_1 (cost=0.00..64,427.98 rows=498,398 width=70) (actual time=0.035..402.177 rows=396,820 loops=573)

48. 242,516.520 493,096.296 ↓ 1.0 850,354 573

Hash (cost=36,231.36..36,231.36 rows=845,336 width=33) (actual time=860.552..860.552 rows=850,354 loops=573)

  • Buckets: 65536 Batches: 32 Memory Usage: 2206kB
49. 250,579.776 250,579.776 ↓ 1.0 850,354 573

Seq Scan on vendor_po_shipment_d vposi_1 (cost=0.00..36,231.36 rows=845,336 width=33) (actual time=0.041..437.312 rows=850,354 loops=573)

50. 540,612.894 1,102,173.522 ↓ 1.0 1,626,373 573

Hash (cost=66,638.15..66,638.15 rows=1,616,115 width=66) (actual time=1,923.514..1,923.514 rows=1,626,373 loops=573)

  • Buckets: 65536 Batches: 64 Memory Usage: 2941kB
51. 561,560.628 561,560.628 ↓ 1.0 1,626,373 573

Seq Scan on vendor_po_item_color vpoic_1 (cost=0.00..66,638.15 rows=1,616,115 width=66) (actual time=0.028..980.036 rows=1,626,373 loops=573)

52. 386.584 386.584 ↑ 1.0 1 191

Index Scan using item_pk on item (cost=0.42..5.29 rows=1 width=157) (actual time=2.024..2.024 rows=1 loops=191)

  • Index Cond: (sid = (vpoitem.item_sid)::bpchar)
  • Filter: is_latest
53. 38.773 38.773 ↑ 1.0 1 191

Index Scan using hcl_entry_pk on hcl_entry hcl2 (cost=0.41..5.23 rows=1 width=72) (actual time=0.203..0.203 rows=1 loops=191)

  • Index Cond: (sid = (item.product_category)::bpchar)
54. 16.235 16.235 ↑ 1.0 1 191

Index Scan using hcl_entry_pk on hcl_entry hcl5 (cost=0.41..5.86 rows=1 width=33) (actual time=0.085..0.085 rows=1 loops=191)

  • Index Cond: (sid = (vpo.product_category)::bpchar)
  • Filter: (split_part((full_lineage_name)::text, '/'::text, 2) = 'Hardline'::text)
55. 313.622 537.092 ↑ 2.3 9 191

Index Scan using cpm_task_doc_sid_idx on cpm_task cpmtask (cost=0.56..9,377.53 rows=21 width=113) (actual time=2.673..2.812 rows=9 loops=191)

  • Index Cond: (cpm_doc_sid = cpmdoc.sid)
  • Filter: (is_latest AND (SubPlan 1))
  • Rows Removed by Filter: 5
56.          

SubPlan (forIndex Scan)

57. 3.438 223.470 ↑ 1.0 1 1,719

GroupAggregate (cost=0.56..274.08 rows=1 width=72) (actual time=0.130..0.130 rows=1 loops=1,719)

  • Group Key: b.ref_doc_id, b.task_sid
58. 220.032 220.032 ↑ 1.0 1 1,719

Index Scan using cpm_task_doc_sid_idx on cpm_task b (cost=0.56..274.06 rows=1 width=45) (actual time=0.013..0.128 rows=1 loops=1,719)

  • Index Cond: (cpmtask.cpm_doc_sid = cpm_doc_sid)
  • Filter: (is_latest AND (cpmtask.ref_doc_id = ref_doc_id) AND ((cpmtask.task_sid)::text = (task_sid)::text))
  • Rows Removed by Filter: 13