explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R9Uk : 1

Settings
# exclusive inclusive rows x rows loops node
1. 0.160 20,572.962 ↓ 0.0 0 1

Nested Loop (cost=292.43..12,593.07 rows=1 width=188) (actual time=20,572.962..20,572.962 rows=0 loops=1)

  • Join Filter: (((facinfo.facility_id_)::text = (prostosta.facility_id_)::text) AND ((product.product_id_)::text = (prostosta.product_id_)::text))
  • Rows Removed by Join Filter: 68
2. 0.068 19,734.857 ↓ 27.0 27 1

Nested Loop Left Join (cost=291.89..2,251.31 rows=1 width=331) (actual time=19,681.073..19,734.857 rows=27 loops=1)

3. 0.083 19,734.708 ↓ 27.0 27 1

Nested Loop Left Join (cost=291.33..2,248.39 rows=1 width=326) (actual time=19,681.067..19,734.708 rows=27 loops=1)

4. 10.452 19,734.517 ↓ 27.0 27 1

Nested Loop (cost=290.78..2,245.47 rows=1 width=321) (actual time=19,681.051..19,734.517 rows=27 loops=1)

5. 41.041 9,010.104 ↓ 13,459.8 53,839 1

Nested Loop (cost=290.22..2,233.76 rows=4 width=353) (actual time=10.640..9,010.104 rows=53,839 loops=1)

6. 35.290 8,699.868 ↓ 13,459.8 53,839 1

Nested Loop (cost=289.81..2,223.27 rows=4 width=243) (actual time=10.004..8,699.868 rows=53,839 loops=1)

7. 58.183 7,803.154 ↓ 13,459.8 53,839 1

Nested Loop (cost=289.25..2,211.94 rows=4 width=162) (actual time=9.985..7,803.154 rows=53,839 loops=1)

8. 24.184 7,637.293 ↓ 13,459.8 53,839 1

Nested Loop (cost=289.11..2,204.46 rows=4 width=103) (actual time=9.976..7,637.293 rows=53,839 loops=1)

9. 14.528 4,272.299 ↓ 144.7 6,655 1

Hash Join (cost=288.56..1,574.69 rows=46 width=140) (actual time=9.951..4,272.299 rows=6,655 loops=1)

  • Hash Cond: ((salesorder.customer_id_)::text = (partyinfo.party_id_)::text)
10. 16.734 4,248.533 ↓ 47.9 6,655 1

Nested Loop (cost=0.99..1,286.14 rows=139 width=119) (actual time=0.694..4,248.533 rows=6,655 loops=1)

11. 12.529 12.529 ↓ 47.9 6,655 1

Index Scan using order_info_idx01 on order_info orderinfo (cost=0.42..648.13 rows=139 width=81) (actual time=0.031..12.529 rows=6,655 loops=1)

  • Index Cond: (((site_id_)::text = 'VA2301'::text) AND ((order_category_id_)::text = 'C044ODTYPESO'::text) AND ((serial_item_flag_)::text = '1'::text))
12. 4,219.270 4,219.270 ↑ 1.0 1 6,655

Index Scan using pk_sales_order on sales_order salesorder (cost=0.56..4.58 rows=1 width=38) (actual time=0.628..0.634 rows=1 loops=6,655)

  • Index Cond: ((order_id_)::text = (orderinfo.order_id_)::text)
13. 0.614 9.238 ↑ 1.0 1,475 1

Hash (cost=269.07..269.07 rows=1,480 width=94) (actual time=9.238..9.238 rows=1,475 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 187kB
14. 1.306 8.624 ↑ 1.0 1,475 1

Hash Join (cost=74.30..269.07 rows=1,480 width=94) (actual time=4.701..8.624 rows=1,475 loops=1)

  • Hash Cond: ((partyinfo.party_id_)::text = (originfo.party_id_)::text)
15. 2.641 2.641 ↓ 1.0 4,508 1

Seq Scan on party_info partyinfo (cost=0.00..163.07 rows=4,507 width=41) (actual time=0.008..2.641 rows=4,508 loops=1)

16. 0.443 4.677 ↑ 1.0 1,480 1

Hash (cost=55.80..55.80 rows=1,480 width=53) (actual time=4.677..4.677 rows=1,480 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 125kB
17. 4.234 4.234 ↑ 1.0 1,480 1

Seq Scan on organization_info originfo (cost=0.00..55.80 rows=1,480 width=53) (actual time=0.003..4.234 rows=1,480 loops=1)

18. 3,340.810 3,340.810 ↑ 7.0 8 6,655

Index Only Scan using sales_order_item_idx02 on sales_order_item salordite (cost=0.55..13.13 rows=56 width=68) (actual time=0.430..0.502 rows=8 loops=6,655)

  • Index Cond: (sales_order_id_ = (salesorder.order_id_)::text)
  • Heap Fetches: 79
19. 107.678 107.678 ↑ 1.0 1 53,839

Index Scan using pk_facility_info on facility_info facinfo (cost=0.14..1.86 rows=1 width=59) (actual time=0.002..0.002 rows=1 loops=53,839)

  • Index Cond: ((facility_id_)::text = (orderinfo.delivery_facility_)::text)
20. 861.424 861.424 ↑ 1.0 1 53,839

Index Scan using pk_retail_order_item on order_item orderitem (cost=0.56..2.82 rows=1 width=81) (actual time=0.015..0.016 rows=1 loops=53,839)

  • Index Cond: ((order_item_id_)::text = (salordite.order_item_id_)::text)
21. 269.195 269.195 ↑ 1.0 1 53,839

Index Scan using pk_product on product (cost=0.42..2.61 rows=1 width=110) (actual time=0.004..0.005 rows=1 loops=53,839)

  • Index Cond: ((product_id_)::text = (orderitem.product_id_)::text)
22. 10,713.961 10,713.961 ↓ 0.0 0 53,839

Index Scan using order_item_progress_idx01 on order_item_progress boorderitrmproqty (cost=0.56..2.92 rows=1 width=42) (actual time=0.199..0.199 rows=0 loops=53,839)

  • Index Cond: (((order_item_id_)::text = (orderitem.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041BACKORDERQTY'::text))
  • Filter: (quantity_ > 0::numeric)
23. 0.108 0.108 ↓ 0.0 0 27

Index Scan using order_item_progress_idx01 on order_item_progress alloorderitrmproqty (cost=0.56..2.91 rows=1 width=42) (actual time=0.004..0.004 rows=0 loops=27)

  • Index Cond: (((order_item_id_)::text = (orderitem.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041ALLOCATEDQTY'::text))
24. 0.081 0.081 ↓ 0.0 0 27

Index Scan using order_item_progress_idx01 on order_item_progress pickorderitrmproqty (cost=0.56..2.91 rows=1 width=42) (actual time=0.003..0.003 rows=0 loops=27)

  • Index Cond: (((order_item_id_)::text = (orderitem.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041ONSHIPPINGQTY'::text))
25. 837.945 837.945 ↑ 1.0 3 27

Index Only Scan using product_stock_status_idx02 on product_stock_status prostosta (cost=0.55..10,340.15 rows=3 width=78) (actual time=23.402..31.035 rows=3 loops=27)

  • Index Cond: ((product_id_ = (orderitem.product_id_)::text) AND (product_stock_status_type_id_ = 'C071ONHANDQTY'::text) AND (quantity_ > 0::numeric))
  • Heap Fetches: 50
26.          

SubPlan (forNested Loop)

27. 0.000 0.000 ↓ 0.0 0

Seq Scan on color_info colorinfo (cost=0.00..1.56 rows=1 width=98) (never executed)

  • Filter: ((color_id_)::text = (product.color_id_)::text)
Planning time : 4.518 ms
Execution time : 20,573.267 ms