explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7hnk : 2

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 3,490.314 ↓ 7.0 7 1

Nested Loop Left Join (cost=5.01..13,602.34 rows=1 width=249) (actual time=3,092.176..3,490.314 rows=7 loops=1)

2. 0.026 3,490.272 ↓ 7.0 7 1

Nested Loop Left Join (cost=4.45..13,599.43 rows=1 width=244) (actual time=3,092.168..3,490.272 rows=7 loops=1)

3. 1.402 3,490.169 ↓ 7.0 7 1

Nested Loop (cost=3.90..13,596.53 rows=1 width=239) (actual time=3,092.152..3,490.169 rows=7 loops=1)

  • Join Filter: ((partyinfo.party_id_)::text = (originfo.party_id_)::text)
  • Rows Removed by Join Filter: 10360
4. 0.126 3,487.570 ↓ 7.0 7 1

Nested Loop (cost=3.90..13,523.23 rows=1 width=259) (actual time=3,092.049..3,487.570 rows=7 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: 48
5. 17.647 3,073.144 ↓ 20.0 20 1

Nested Loop (cost=3.35..3,267.05 rows=1 width=402) (actual time=3,007.006..3,073.144 rows=20 loops=1)

6. 9.927 2,781.547 ↓ 4,214.6 54,790 1

Nested Loop (cost=2.79..3,229.21 rows=13 width=434) (actual time=1,629.840..2,781.547 rows=54,790 loops=1)

7. 286.158 2,662.040 ↓ 4,214.6 54,790 1

Nested Loop Left Join (cost=2.51..3,197.47 rows=13 width=393) (actual time=1,629.829..2,662.040 rows=54,790 loops=1)

  • Join Filter: ((colorinfo.color_id_)::text = (product.color_id_)::text)
  • Rows Removed by Join Filter: 2410760
8. 15.544 2,211.512 ↓ 4,214.6 54,790 1

Nested Loop (cost=2.51..3,187.14 rows=13 width=332) (actual time=1,629.811..2,211.512 rows=54,790 loops=1)

9. 46.705 2,031.598 ↓ 4,214.6 54,790 1

Nested Loop (cost=2.09..3,153.35 rows=13 width=222) (actual time=1,629.797..2,031.598 rows=54,790 loops=1)

10. 796.655 1,710.943 ↓ 4,214.6 54,790 1

Nested Loop (cost=1.54..3,116.80 rows=13 width=141) (actual time=1,629.770..1,710.943 rows=54,790 loops=1)

  • Join Filter: ((orderinfo.delivery_facility_)::text = (facinfo.facility_id_)::text)
  • Rows Removed by Join Filter: 6520010
11. 0.128 0.128 ↓ 1.0 120 1

Seq Scan on facility_info facinfo (cost=0.00..4.16 rows=116 width=59) (actual time=0.006..0.128 rows=120 loops=1)

12. 799.982 914.160 ↓ 4,214.6 54,790 120

Materialize (cost=1.54..3,090.06 rows=13 width=82) (actual time=0.003..7.618 rows=54,790 loops=120)

13. 6.968 114.178 ↓ 4,214.6 54,790 1

Nested Loop (cost=1.54..3,089.99 rows=13 width=82) (actual time=0.053..114.178 rows=54,790 loops=1)

14. 3.911 53.122 ↓ 42.5 6,761 1

Nested Loop (cost=0.99..1,427.20 rows=159 width=119) (actual time=0.033..53.122 rows=6,761 loops=1)

15. 8.645 8.645 ↓ 42.5 6,761 1

Index Scan using order_info_idx01 on order_info orderinfo (cost=0.42..697.39 rows=159 width=81) (actual time=0.017..8.645 rows=6,761 loops=1)

  • Index Cond: (((site_id_)::text = 'VA2301'::text) AND ((order_category_id_)::text = 'C044ODTYPESO'::text) AND ((serial_item_flag_)::text = '1'::text))
16. 40.566 40.566 ↑ 1.0 1 6,761

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

  • Index Cond: ((order_id_)::text = (orderinfo.order_id_)::text)
17. 54.088 54.088 ↑ 7.1 8 6,761

Index Only Scan using sales_order_item_idx02 on sales_order_item salordite (cost=0.55..9.89 rows=57 width=68) (actual time=0.005..0.008 rows=8 loops=6,761)

  • Index Cond: (sales_order_id_ = (salesorder.order_id_)::text)
  • Heap Fetches: 54790
18. 273.950 273.950 ↑ 1.0 1 54,790

Index Scan using pk_retail_order_item on order_item orderitem (cost=0.56..2.80 rows=1 width=81) (actual time=0.005..0.005 rows=1 loops=54,790)

  • Index Cond: ((order_item_id_)::text = (salordite.order_item_id_)::text)
19. 164.370 164.370 ↑ 1.0 1 54,790

Index Scan using pk_product on product (cost=0.42..2.59 rows=1 width=110) (actual time=0.002..0.003 rows=1 loops=54,790)

  • Index Cond: ((product_id_)::text = (orderitem.product_id_)::text)
20. 164.356 164.370 ↑ 1.0 45 54,790

Materialize (cost=0.00..1.68 rows=45 width=196) (actual time=0.000..0.003 rows=45 loops=54,790)

21. 0.014 0.014 ↑ 1.0 45 1

Seq Scan on color_info colorinfo (cost=0.00..1.45 rows=45 width=196) (actual time=0.005..0.014 rows=45 loops=1)

22. 109.580 109.580 ↑ 1.0 1 54,790

Index Scan using pk_party_info on party_info partyinfo (cost=0.28..2.43 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=54,790)

  • Index Cond: ((party_id_)::text = (salesorder.customer_id_)::text)
23. 273.950 273.950 ↓ 0.0 0 54,790

Index Scan using order_item_progress_idx01 on order_item_progress boorderitrmproqty (cost=0.56..2.90 rows=1 width=42) (actual time=0.005..0.005 rows=0 loops=54,790)

  • Index Cond: (((order_item_id_)::text = (orderitem.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041BACKORDERQTY'::text))
  • Filter: (quantity_ > 0::numeric)
24. 414.300 414.300 ↑ 1.0 3 20

Index Only Scan using product_stock_status_idx02 on product_stock_status prostosta (cost=0.55..10,256.14 rows=3 width=78) (actual time=13.988..20.715 rows=3 loops=20)

  • Index Cond: ((product_id_ = (orderitem.product_id_)::text) AND (product_stock_status_type_id_ = 'C071ONHANDQTY'::text) AND (quantity_ > 0::numeric))
  • Heap Fetches: 50
25. 1.197 1.197 ↓ 1.0 1,481 7

Seq Scan on organization_info originfo (cost=0.00..54.80 rows=1,480 width=53) (actual time=0.002..0.171 rows=1,481 loops=7)

26. 0.077 0.077 ↓ 0.0 0 7

Index Scan using order_item_progress_idx01 on order_item_progress alloorderitrmproqty (cost=0.56..2.90 rows=1 width=42) (actual time=0.011..0.011 rows=0 loops=7)

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

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

  • Index Cond: (((order_item_id_)::text = (orderitem.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041ONSHIPPINGQTY'::text))