explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ogf : 2

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

Sort (cost=3,856.86..3,856.86 rows=3 width=409) (actual time=528.321..532.458 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. 3.044 3.044 ↓ 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.032..3.044 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. 0.956 477.211 ↓ 4,749.7 14,249 1

Nested Loop Left Join (cost=1,293.16..2,858.00 rows=3 width=409) (actual time=399.793..477.211 rows=14,249 loops=1)

5. 8.505 447.757 ↓ 4,749.7 14,249 1

Nested Loop Left Join (cost=1,292.88..2,857.05 rows=3 width=432) (actual time=399.777..447.757 rows=14,249 loops=1)

6. 28.589 425.003 ↓ 4,749.7 14,249 1

Hash Right Join (cost=1,292.74..2,856.55 rows=3 width=371) (actual time=399.757..425.003 rows=14,249 loops=1)

  • Hash Cond: ((dr.delivery_order_id_)::text = (temp.delivery_order_id_)::text)
7. 12.270 12.270 ↑ 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.019..12.270 rows=34,969 loops=1)

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

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

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

Nested Loop (cost=2.93..1,292.70 rows=3 width=436) (actual time=0.176..366.814 rows=14,249 loops=1)

10. 4.939 333.667 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.66..1,289.66 rows=9 width=553) (actual time=0.164..333.667 rows=14,249 loops=1)

11. 0.967 300.230 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.37..1,261.69 rows=9 width=512) (actual time=0.153..300.230 rows=14,249 loops=1)

12. 8.114 270.765 ↓ 1,583.2 14,249 1

Nested Loop (cost=2.10..1,256.28 rows=9 width=596) (actual time=0.144..270.765 rows=14,249 loops=1)

13. 3.843 219.904 ↓ 1,583.2 14,249 1

Nested Loop (cost=1.69..1,252.00 rows=9 width=523) (actual time=0.130..219.904 rows=14,249 loops=1)

14. 3.470 116.311 ↓ 233.6 14,250 1

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

15. 7.013 64.009 ↓ 67.7 6,976 1

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

16. 2.937 22.116 ↓ 19.0 6,976 1

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

17. 4.763 4.763 ↓ 24.7 1,802 1

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

18. 14.416 14.416 ↑ 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.008 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 Scan using seria_pro_ide_idx99 on serialized_product_identifier (cost=0.43..1.30 rows=1 width=54) (actual time=0.006..0.007 rows=1 loops=14,250)

  • Index Cond: (((site_id_)::text = 'VA2301'::text) AND ((serialized_product_id_)::text = (dsi.serialized_product_id_)::text))
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. 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 : 4.818 ms
Execution time : 534.205 ms