explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QQvQ : 5

Settings
# exclusive inclusive rows x rows loops node
1. 58.524 665.147 ↓ 4,749.7 14,249 1

Sort (cost=3,830.34..3,830.34 rows=3 width=409) (actual time=661.212..665.147 rows=14,249 loops=1)

  • Sort Key: party.party_code_, pro.product_code_, temp.delivery_no_, serialized_product_identifier.frame_no_
  • Sort Method: external merge Disk: 3688kB
2.          

CTE temp

3. 4.350 4.350 ↓ 24.7 1,802 1

Index Scan using delivery_order_idx02 on delivery_order deo (cost=0.42..998.84 rows=73 width=139) (actual time=0.085..4.350 rows=1,802 loops=1)

  • Index Cond: (((finish_date_)::text >= '20190101'::text) AND ((finish_date_)::text <= '20190201'::text) AND ((site_id_)::text = 'VA2301'::text))
  • Filter: (((serial_item_flag_)::text = '1'::text) AND ((inventory_transaction_type_id_)::text = 'C020SALESTOCKOUT'::text) AND ((delivery_status_id_)::text = 'C087SHIPPINGCOMPLETION'::text))
4. 2.581 606.623 ↓ 4,749.7 14,249 1

Nested Loop Left Join (cost=1,265.05..2,831.48 rows=3 width=409) (actual time=523.439..606.623 rows=14,249 loops=1)

5. 9.053 575.544 ↓ 4,749.7 14,249 1

Nested Loop Left Join (cost=1,264.77..2,830.53 rows=3 width=432) (actual time=523.423..575.544 rows=14,249 loops=1)

6. 31.776 552.242 ↓ 4,749.7 14,249 1

Hash Right Join (cost=1,264.63..2,830.03 rows=3 width=371) (actual time=523.405..552.242 rows=14,249 loops=1)

  • Hash Cond: ((dr.delivery_order_id_)::text = (temp.delivery_order_id_)::text)
7. 12.589 12.589 ↑ 1.0 34,969 1

Seq Scan on delivery_role dr (cost=0.00..1,433.89 rows=35,061 width=70) (actual time=0.015..12.589 rows=34,969 loops=1)

  • Filter: ((order_role_type_id_)::text = 'C026SHIPMENTPIC'::text)
  • Rows Removed by Filter: 526
8. 25.533 507.877 ↓ 4,749.7 14,249 1

Hash (cost=1,264.60..1,264.60 rows=3 width=436) (actual time=507.877..507.877 rows=14,249 loops=1)

  • Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 4097kB
9. 17.888 482.344 ↓ 4,749.7 14,249 1

Nested Loop (cost=3.06..1,264.60 rows=3 width=436) (actual time=0.228..482.344 rows=14,249 loops=1)

10. 17.517 435.958 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.78..1,261.55 rows=9 width=553) (actual time=0.220..435.958 rows=14,249 loops=1)

11. 12.788 389.943 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.50..1,233.59 rows=9 width=512) (actual time=0.199..389.943 rows=14,249 loops=1)

12. 12.033 348.657 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.23..1,228.18 rows=9 width=596) (actual time=0.188..348.657 rows=14,249 loops=1)

13. 13.941 279.628 ↓ 1,583.2 14,249 1

Nested Loop (cost=1.81..1,223.90 rows=9 width=523) (actual time=0.174..279.628 rows=14,249 loops=1)

14. 11.451 151.687 ↓ 237.5 14,250 1

Nested Loop (cost=1.26..1,172.08 rows=60 width=542) (actual time=0.146..151.687 rows=14,250 loops=1)

15. 4.840 84.428 ↓ 67.7 6,976 1

Nested Loop (cost=0.84..1,114.42 rows=103 width=543) (actual time=0.128..84.428 rows=6,976 loops=1)

16. 3.885 30.756 ↓ 19.0 6,976 1

Nested Loop (cost=0.42..901.05 rows=368 width=470) (actual time=0.114..30.756 rows=6,976 loops=1)

17. 7.049 7.049 ↓ 24.7 1,802 1

CTE Scan on temp (cost=0.00..1.46 rows=73 width=386) (actual time=0.090..7.049 rows=1,802 loops=1)

18. 19.822 19.822 ↑ 1.2 4 1,802

Index Scan using delivery_order_item_idx01 on delivery_order_item doi (cost=0.42..12.27 rows=5 width=121) (actual time=0.008..0.011 rows=4 loops=1,802)

  • Index Cond: ((delivery_order_id_)::text = (temp.delivery_order_id_)::text)
19. 48.832 48.832 ↑ 1.0 1 6,976

Index Scan using order_delivery_item_relation_idx_12 on order_delivery_item_relation itemrelation (cost=0.42..0.57 rows=1 width=73) (actual time=0.006..0.007 rows=1 loops=6,976)

  • Index Cond: ((delivery_order_item_id_)::text = (doi.delivery_order_item_id_)::text)
  • Filter: ((site_id_)::text = 'VA2301'::text)
20. 55.808 55.808 ↑ 1.0 2 6,976

Index Scan using delivery_serialized_item_idx99 on delivery_serialized_item dsi (cost=0.42..0.54 rows=2 width=73) (actual time=0.007..0.008 rows=2 loops=6,976)

  • Index Cond: (((site_id_)::text = 'VA2301'::text) AND ((delivery_order_item_id_)::text = (doi.delivery_order_item_id_)::text))
21. 114.000 114.000 ↑ 1.0 1 14,250

Index Only Scan using seria_pro_ide_idx99 on serialized_product_identifier (cost=0.55..0.85 rows=1 width=54) (actual time=0.008..0.008 rows=1 loops=14,250)

  • Index Cond: ((site_id_ = 'VA2301'::text) AND (serialized_product_id_ = (dsi.serialized_product_id_)::text))
  • Heap Fetches: 14249
22. 56.996 56.996 ↑ 1.0 1 14,249

Index Scan using pk_product on product pro (cost=0.41..0.47 rows=1 width=110) (actual time=0.004..0.004 rows=1 loops=14,249)

  • Index Cond: ((product_id_)::text = (doi.product_id_)::text)
23. 28.498 28.498 ↑ 1.0 1 14,249

Index Scan using pk_facility_info on facility_info fi (cost=0.27..0.59 rows=1 width=51) (actual time=0.002..0.002 rows=1 loops=14,249)

  • Index Cond: ((facility_id_)::text = (temp.from_facility_id_)::text)
24. 28.498 28.498 ↑ 1.0 1 14,249

Index Scan using pk_party_info on party_info party (cost=0.28..3.10 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=14,249)

  • Index Cond: ((party_id_)::text = (temp.to_organization_id_)::text)
25. 28.498 28.498 ↑ 1.0 1 14,249

Index Scan using pk_organization_info on organization_info oi (cost=0.28..0.33 rows=1 width=53) (actual time=0.002..0.002 rows=1 loops=14,249)

  • Index Cond: ((party_id_)::text = (party.party_id_)::text)
26. 14.249 14.249 ↑ 1.0 1 14,249

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

  • Index Cond: ((color_id_)::text = (pro.color_id_)::text)
27. 28.498 28.498 ↑ 1.0 1 14,249

Index Scan using pk_person on person per (cost=0.28..0.30 rows=1 width=47) (actual time=0.001..0.002 rows=1 loops=14,249)

  • Index Cond: ((party_id_)::text = (dr.party_id_)::text)
Planning time : 6.836 ms
Execution time : 667.176 ms