explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i1Nu : 111

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

Sort (cost=2,263.45..2,263.46 rows=3 width=301) (actual time=392.532..397.423 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: 3376kB
2.          

CTE temp

3. 2.799 2.799 ↓ 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.030..2.799 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. 3.307 337.648 ↓ 4,749.7 14,249 1

Nested Loop (cost=3.06..1,264.60 rows=3 width=301) (actual time=0.118..337.648 rows=14,249 loops=1)

5. 3.456 305.843 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.78..1,261.55 rows=9 width=418) (actual time=0.112..305.843 rows=14,249 loops=1)

6. 13.983 273.889 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.50..1,233.59 rows=9 width=377) (actual time=0.104..273.889 rows=14,249 loops=1)

7. 5.205 245.657 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.23..1,228.18 rows=9 width=461) (actual time=0.096..245.657 rows=14,249 loops=1)

8. 5.639 197.705 ↓ 1,583.2 14,249 1

Nested Loop (cost=1.81..1,223.90 rows=9 width=425) (actual time=0.083..197.705 rows=14,249 loops=1)

9. 6.758 106.566 ↓ 237.5 14,250 1

Nested Loop (cost=1.26..1,172.08 rows=60 width=444) (actual time=0.068..106.566 rows=14,250 loops=1)

10. 2.853 57.952 ↓ 67.7 6,976 1

Nested Loop (cost=0.84..1,114.42 rows=103 width=445) (actual time=0.057..57.952 rows=6,976 loops=1)

11. 3.170 20.219 ↓ 19.0 6,976 1

Nested Loop (cost=0.42..901.05 rows=368 width=372) (actual time=0.046..20.219 rows=6,976 loops=1)

12. 4.435 4.435 ↓ 24.7 1,802 1

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

13. 12.614 12.614 ↑ 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.005..0.007 rows=4 loops=1,802)

  • Index Cond: ((delivery_order_id_)::text = (temp.delivery_order_id_)::text)
14. 34.880 34.880 ↑ 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.005..0.005 rows=1 loops=6,976)

  • Index Cond: ((delivery_order_item_id_)::text = (doi.delivery_order_item_id_)::text)
  • Filter: ((site_id_)::text = 'VA2301'::text)
15. 41.856 41.856 ↑ 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.005..0.006 rows=2 loops=6,976)

  • Index Cond: (((site_id_)::text = 'VA2301'::text) AND ((delivery_order_item_id_)::text = (doi.delivery_order_item_id_)::text))
16. 85.500 85.500 ↑ 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.006..0.006 rows=1 loops=14,250)

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

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

  • Index Cond: ((product_id_)::text = (doi.product_id_)::text)
18. 14.249 14.249 ↑ 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.001..0.001 rows=1 loops=14,249)

  • Index Cond: ((facility_id_)::text = (temp.from_facility_id_)::text)
19. 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)
20. 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.001..0.002 rows=1 loops=14,249)

  • Index Cond: ((party_id_)::text = (party.party_id_)::text)
Planning time : 3.783 ms
Execution time : 399.575 ms