explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Smqi : V1

Settings
# exclusive inclusive rows x rows loops node
1. 11.175 256.227 ↓ 3,721.0 3,721 1

Sort (cost=771.16..771.16 rows=1 width=289) (actual time=255.999..256.227 rows=3,721 loops=1)

  • Sort Key: fi.description_, pr.product_code_, temp.order_no_
  • Sort Method: quicksort Memory: 1085kB
2.          

CTE temp

3. 3.701 3.701 ↓ 54.1 379 1

Index Scan using order_info_idx03 on order_info oi_1 (cost=0.42..646.44 rows=7 width=102) (actual time=0.433..3.701 rows=379 loops=1)

  • Index Cond: (((order_date_)::text >= '20190201'::text) AND ((order_date_)::text <= '20190301'::text) AND ((site_id_)::text = 'VA2301'::text) AND ((serial_item_flag_)::text = '1'::text))
  • Filter: ((order_category_id_)::text = 'C044ODTYPESO'::text)
  • Rows Removed by Filter: 3
4. 9.336 245.052 ↓ 3,721.0 3,721 1

Nested Loop Left Join (cost=3.34..124.71 rows=1 width=289) (actual time=0.900..245.052 rows=3,721 loops=1)

5. 4.173 138.970 ↓ 3,721.0 3,721 1

Nested Loop (cost=3.20..106.23 rows=1 width=228) (actual time=0.761..138.970 rows=3,721 loops=1)

6. 3.530 119.913 ↓ 3,721.0 3,721 1

Nested Loop (cost=2.78..105.09 rows=1 width=191) (actual time=0.716..119.913 rows=3,721 loops=1)

7. 4.327 108.941 ↓ 3,721.0 3,721 1

Nested Loop (cost=2.50..104.14 rows=1 width=211) (actual time=0.687..108.941 rows=3,721 loops=1)

8. 3.108 97.172 ↓ 3,721.0 3,721 1

Nested Loop Left Join (cost=2.22..103.16 rows=1 width=170) (actual time=0.664..97.172 rows=3,721 loops=1)

9. 2.985 86.622 ↓ 3,721.0 3,721 1

Nested Loop (cost=1.94..98.86 rows=1 width=258) (actual time=0.633..86.622 rows=3,721 loops=1)

10. 1.956 76.195 ↓ 3,721.0 3,721 1

Nested Loop (cost=1.67..95.70 rows=1 width=342) (actual time=0.609..76.195 rows=3,721 loops=1)

11. 1.200 22.145 ↓ 3,721.0 3,721 1

Nested Loop (cost=1.12..94.18 rows=1 width=292) (actual time=0.569..22.145 rows=3,721 loops=1)

12. 0.540 11.849 ↓ 54.1 379 1

Nested Loop (cost=0.56..32.27 rows=7 width=390) (actual time=0.500..11.849 rows=379 loops=1)

13. 4.108 4.108 ↓ 54.1 379 1

CTE Scan on temp (cost=0.00..0.14 rows=7 width=352) (actual time=0.436..4.108 rows=379 loops=1)

14. 7.201 7.201 ↑ 1.0 1 379

Index Scan using pk_sales_order on sales_order so (cost=0.56..4.58 rows=1 width=38) (actual time=0.019..0.019 rows=1 loops=379)

  • Index Cond: ((order_id_)::text = (temp.order_id_)::text)
15. 9.096 9.096 ↑ 5.8 10 379

Index Only Scan using sales_order_item_idx02 on sales_order_item soi (cost=0.56..8.26 rows=58 width=68) (actual time=0.018..0.024 rows=10 loops=379)

  • Index Cond: (sales_order_id_ = (so.order_id_)::text)
  • Heap Fetches: 3721
16. 52.094 52.094 ↑ 1.0 1 3,721

Index Scan using pk_retail_order_item on order_item oit (cost=0.56..1.51 rows=1 width=87) (actual time=0.013..0.014 rows=1 loops=3,721)

  • Index Cond: ((order_item_id_)::text = (soi.order_item_id_)::text)
17. 7.442 7.442 ↑ 1.0 1 3,721

Index Scan using pk_facility_info on facility_info fi (cost=0.27..3.15 rows=1 width=51) (actual time=0.002..0.002 rows=1 loops=3,721)

  • Index Cond: ((facility_id_)::text = (temp.delivery_facility_)::text)
18. 7.442 7.442 ↑ 1.0 1 3,721

Index Scan using pk_person on person p (cost=0.28..4.30 rows=1 width=47) (actual time=0.002..0.002 rows=1 loops=3,721)

  • Index Cond: ((party_id_)::text = (temp.entry_pic_)::text)
19. 7.442 7.442 ↑ 1.0 1 3,721

Index Scan using pk_party_info on party_info pi (cost=0.28..0.97 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=3,721)

  • Index Cond: ((party_id_)::text = (so.customer_id_)::text)
20. 7.442 7.442 ↑ 1.0 1 3,721

Index Scan using pk_organization_info on organization_info oi (cost=0.28..0.93 rows=1 width=53) (actual time=0.002..0.002 rows=1 loops=3,721)

  • Index Cond: ((party_id_)::text = (so.customer_id_)::text)
21. 14.884 14.884 ↑ 1.0 1 3,721

Index Scan using pk_product on product pr (cost=0.41..1.13 rows=1 width=110) (actual time=0.004..0.004 rows=1 loops=3,721)

  • Index Cond: ((product_id_)::text = (oit.product_id_)::text)
22. 7.442 7.442 ↑ 1.0 1 3,721

Index Scan using pk_color_info on color_info ci (cost=0.14..0.16 rows=1 width=196) (actual time=0.001..0.002 rows=1 loops=3,721)

  • Index Cond: ((color_id_)::text = (pr.color_id_)::text)
23.          

SubPlan (forNested Loop Left Join)

24. 55.815 55.815 ↓ 0.0 0 3,721

Index Scan using order_item_progress_idx01 on order_item_progress oip (cost=0.56..4.58 rows=1 width=5) (actual time=0.015..0.015 rows=0 loops=3,721)

  • Index Cond: (((order_item_id_)::text = (soi.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041BACKORDERQTY'::text))
25. 11.163 11.163 ↓ 0.0 0 3,721

Index Scan using order_item_progress_idx01 on order_item_progress oip1 (cost=0.56..4.58 rows=1 width=5) (actual time=0.003..0.003 rows=0 loops=3,721)

  • Index Cond: (((order_item_id_)::text = (soi.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041ALLOCATEDQTY'::text))
26. 7.442 7.442 ↓ 0.0 0 3,721

Index Scan using order_item_progress_idx01 on order_item_progress oip2 (cost=0.56..4.58 rows=1 width=5) (actual time=0.002..0.002 rows=0 loops=3,721)

  • Index Cond: (((order_item_id_)::text = (soi.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041ONSHIPPINGQTY'::text))
27. 14.884 14.884 ↑ 1.0 1 3,721

Index Scan using order_item_progress_idx01 on order_item_progress oip3 (cost=0.56..4.58 rows=1 width=5) (actual time=0.003..0.004 rows=1 loops=3,721)

  • Index Cond: (((order_item_id_)::text = (soi.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041SHIPPEDQTY'::text))
Planning time : 7.326 ms
Execution time : 256.749 ms