explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xgQ : 4

Settings
# exclusive inclusive rows x rows loops node
1. 0.113 1,742.417 ↓ 0.0 0 1

Nested Loop (cost=5.28..11,042.97 rows=1 width=188) (actual time=1,742.417..1,742.417 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.065 1,199.820 ↓ 27.0 27 1

Nested Loop Left Join (cost=4.74..701.20 rows=1 width=331) (actual time=1,187.615..1,199.820 rows=27 loops=1)

3. 0.052 1,199.674 ↓ 27.0 27 1

Nested Loop Left Join (cost=4.18..698.20 rows=1 width=326) (actual time=1,187.604..1,199.674 rows=27 loops=1)

4. 30.506 1,199.541 ↓ 27.0 27 1

Nested Loop (cost=3.63..695.19 rows=1 width=321) (actual time=1,187.596..1,199.541 rows=27 loops=1)

5. 1.192 899.840 ↓ 53,839.0 53,839 1

Nested Loop (cost=3.07..692.18 rows=1 width=353) (actual time=0.262..899.840 rows=53,839 loops=1)

6. 2.105 790.970 ↓ 53,839.0 53,839 1

Nested Loop (cost=2.79..689.71 rows=1 width=385) (actual time=0.254..790.970 rows=53,839 loops=1)

7. 23.027 681.187 ↓ 53,839.0 53,839 1

Nested Loop (cost=2.51..687.29 rows=1 width=332) (actual time=0.202..681.187 rows=53,839 loops=1)

8. 46.468 496.643 ↓ 53,839.0 53,839 1

Nested Loop (cost=2.09..684.67 rows=1 width=222) (actual time=0.187..496.643 rows=53,839 loops=1)

9. 8.189 127.141 ↓ 53,839.0 53,839 1

Nested Loop (cost=1.54..681.79 rows=1 width=141) (actual time=0.140..127.141 rows=53,839 loops=1)

10. 1.721 59.057 ↓ 6,655.0 6,655 1

Nested Loop (cost=0.99..677.21 rows=1 width=178) (actual time=0.118..59.057 rows=6,655 loops=1)

11. 10.030 17.406 ↓ 1,663.8 6,655 1

Nested Loop (cost=0.42..658.84 rows=4 width=140) (actual time=0.104..17.406 rows=6,655 loops=1)

  • Join Filter: ((orderinfo.delivery_facility_)::text = (facinfo.facility_id_)::text)
  • Rows Removed by Join Filter: 13310
12. 7.376 7.376 ↓ 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.027..7.376 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))
13. 0.000 0.000 ↑ 1.0 3 6,655

Materialize (cost=0.00..4.46 rows=3 width=59) (actual time=0.000..0.000 rows=3 loops=6,655)

14. 0.067 0.067 ↑ 1.0 3 1

Seq Scan on facility_info facinfo (cost=0.00..4.45 rows=3 width=59) (actual time=0.066..0.067 rows=3 loops=1)

  • Filter: ((site_id_)::text = 'VA2301'::text)
  • Rows Removed by Filter: 117
15. 39.930 39.930 ↑ 1.0 1 6,655

Index Scan using sales_order_idx02 on sales_order salesorder (cost=0.56..4.58 rows=1 width=38) (actual time=0.005..0.006 rows=1 loops=6,655)

  • Index Cond: (((site_id_)::text = 'VA2301'::text) AND ((order_id_)::text = (orderinfo.order_id_)::text))
16. 59.895 59.895 ↑ 1.5 8 6,655

Index Only Scan using sales_order_item_idx02 on sales_order_item salordite (cost=0.55..4.46 rows=12 width=68) (actual time=0.007..0.009 rows=8 loops=6,655)

  • Index Cond: ((sales_order_id_ = (salesorder.order_id_)::text) AND (site_id_ = 'VA2301'::text))
  • Heap Fetches: 79
17. 323.034 323.034 ↑ 1.0 1 53,839

Index Scan using order_item_idx03 on order_item orderitem (cost=0.56..2.87 rows=1 width=81) (actual time=0.006..0.006 rows=1 loops=53,839)

  • Index Cond: (((site_id_)::text = 'VA2301'::text) AND ((order_item_id_)::text = (salordite.order_item_id_)::text))
18. 161.517 161.517 ↑ 1.0 1 53,839

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

  • Index Cond: ((product_id_)::text = (orderitem.product_id_)::text)
19. 107.678 107.678 ↑ 1.0 1 53,839

Index Scan using pk_organization_info on organization_info originfo (cost=0.28..2.41 rows=1 width=53) (actual time=0.001..0.002 rows=1 loops=53,839)

  • Index Cond: ((party_id_)::text = (salesorder.customer_id_)::text)
20. 107.678 107.678 ↑ 1.0 1 53,839

Index Scan using pk_party_info on party_info partyinfo (cost=0.28..2.46 rows=1 width=41) (actual time=0.001..0.002 rows=1 loops=53,839)

  • Index Cond: ((party_id_)::text = (salesorder.customer_id_)::text)
21. 269.195 269.195 ↓ 0.0 0 53,839

Index Scan using order_item_progress_idx01 on order_item_progress boorderitrmproqty (cost=0.56..3.00 rows=1 width=42) (actual time=0.005..0.005 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) AND ((site_id_)::text = 'VA2301'::text))
22. 0.081 0.081 ↓ 0.0 0 27

Index Scan using order_item_progress_idx01 on order_item_progress alloorderitrmproqty (cost=0.56..3.00 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 = 'C041ALLOCATEDQTY'::text))
  • Filter: ((site_id_)::text = 'VA2301'::text)
23. 0.081 0.081 ↓ 0.0 0 27

Index Scan using order_item_progress_idx01 on order_item_progress pickorderitrmproqty (cost=0.56..3.00 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))
  • Filter: ((site_id_)::text = 'VA2301'::text)
24. 542.484 542.484 ↑ 1.0 3 27

Index Only Scan using product_stock_status_idx02 on product_stock_status prostosta (cost=0.55..10,340.16 rows=3 width=78) (actual time=13.681..20.092 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
25.          

SubPlan (forNested Loop)

26. 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 : 11.735 ms
Execution time : 1,742.664 ms