explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kf8t : 4

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

Sort (cost=3,829.71..3,829.71 rows=3 width=409) (actual time=528.660..532.780 rows=14,249 loops=1)

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

CTE temp

3. 3.004 3.004 ↓ 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.042..3.004 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. 14.537 481.816 ↓ 4,749.7 14,249 1

Nested Loop Left Join (cost=1,266.00..2,830.85 rows=3 width=409) (actual time=405.861..481.816 rows=14,249 loops=1)

5. 7.535 453.030 ↓ 4,749.7 14,249 1

Nested Loop Left Join (cost=1,265.72..2,829.90 rows=3 width=432) (actual time=405.847..453.030 rows=14,249 loops=1)

6. 28.760 431.246 ↓ 4,749.7 14,249 1

Hash Right Join (cost=1,265.58..2,829.40 rows=3 width=371) (actual time=405.832..431.246 rows=14,249 loops=1)

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

Seq Scan on delivery_role dr (cost=0.00..1,432.44 rows=35,026 width=70) (actual time=0.016..12.335 rows=34,969 loops=1)

  • Filter: ((order_role_type_id_)::text = 'C026SHIPMENTPIC'::text)
  • Rows Removed by Filter: 462
8. 16.912 390.151 ↓ 4,749.7 14,249 1

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

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

Nested Loop (cost=3.06..1,265.55 rows=3 width=436) (actual time=0.171..373.239 rows=14,249 loops=1)

10. 5.912 339.488 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.78..1,262.51 rows=9 width=553) (actual time=0.165..339.488 rows=14,249 loops=1)

11. 1.498 305.078 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.50..1,234.54 rows=9 width=512) (actual time=0.158..305.078 rows=14,249 loops=1)

12. 8.757 275.082 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.23..1,229.13 rows=9 width=596) (actual time=0.150..275.082 rows=14,249 loops=1)

13. 9.697 223.578 ↓ 1,583.2 14,249 1

Nested Loop (cost=1.81..1,224.85 rows=9 width=523) (actual time=0.140..223.578 rows=14,249 loops=1)

14. 3.439 114.131 ↓ 233.6 14,250 1

Nested Loop (cost=1.26..1,172.17 rows=61 width=542) (actual time=0.089..114.131 rows=14,250 loops=1)

15. 5.474 61.860 ↓ 67.7 6,976 1

Nested Loop (cost=0.84..1,114.49 rows=103 width=543) (actual time=0.079..61.860 rows=6,976 loops=1)

16. 4.155 21.506 ↓ 19.0 6,976 1

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

17. 4.737 4.737 ↓ 24.7 1,802 1

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

18. 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.006..0.007 rows=4 loops=1,802)

  • Index Cond: ((delivery_order_id_)::text = (temp.delivery_order_id_)::text)
19. 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)
20. 48.832 48.832 ↑ 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.006..0.007 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. 99.750 99.750 ↑ 1.0 1 14,250

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

  • Index Cond: ((site_id_ = 'VA2301'::text) AND (serialized_product_id_ = (dsi.serialized_product_id_)::text))
  • Heap Fetches: 14249
22. 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=110) (actual time=0.003..0.003 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.001..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. 14.249 14.249 ↑ 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.001 rows=1 loops=14,249)

  • Index Cond: ((party_id_)::text = (dr.party_id_)::text)
Planning time : 4.600 ms
Execution time : 534.663 ms