explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nbX5 : 1

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

Sort (cost=57,143.39..57,143.40 rows=3 width=409) (actual time=752.987..757.659 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: 3680kB
2.          

CTE temp

3. 2.546 2.546 ↓ 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.074..2.546 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. 13.195 695.155 ↓ 4,749.7 14,249 1

Nested Loop Left Join (cost=4,979.47..56,144.53 rows=3 width=409) (actual time=141.319..695.155 rows=14,249 loops=1)

5. 23.319 653.462 ↓ 4,749.7 14,249 1

Hash Left Join (cost=4,979.19..56,143.59 rows=3 width=432) (actual time=141.286..653.462 rows=14,249 loops=1)

  • Hash Cond: ((temp.delivery_order_id_)::text = (dr.delivery_order_id_)::text)
6. 13.678 598.499 ↓ 4,749.7 14,249 1

Nested Loop Left Join (cost=3,108.93..54,273.21 rows=3 width=497) (actual time=109.595..598.499 rows=14,249 loops=1)

7. 13.938 570.572 ↓ 4,749.7 14,249 1

Nested Loop (cost=3,108.79..54,272.71 rows=3 width=436) (actual time=109.586..570.572 rows=14,249 loops=1)

8. 16.598 528.136 ↓ 1,781.1 14,249 1

Nested Loop (cost=3,108.51..54,270.00 rows=8 width=553) (actual time=109.572..528.136 rows=14,249 loops=1)

9. 5.889 483.040 ↓ 1,781.1 14,249 1

Nested Loop (cost=3,108.23..54,245.15 rows=8 width=512) (actual time=109.558..483.040 rows=14,249 loops=1)

10. 8.040 448.653 ↓ 1,781.1 14,249 1

Nested Loop (cost=3,107.96..54,240.34 rows=8 width=596) (actual time=109.547..448.653 rows=14,249 loops=1)

11. 19.584 397.866 ↓ 1,781.1 14,249 1

Nested Loop (cost=3,107.54..54,236.53 rows=8 width=523) (actual time=109.528..397.866 rows=14,249 loops=1)

  • Join Filter: ((dsi.delivery_order_item_id_)::text = (itemrelation.delivery_order_item_id_)::text)
12. 155.848 307.037 ↓ 475.0 14,249 1

Hash Join (cost=3,107.12..54,219.05 rows=30 width=524) (actual time=109.493..307.037 rows=14,249 loops=1)

  • Hash Cond: ((serialized_product_identifier.serialized_product_id_)::text = (dsi.serialized_product_id_)::text)
13. 49.901 62.269 ↓ 1.0 151,655 1

Bitmap Heap Scan on serialized_product_identifier (cost=1,997.28..52,544.31 rows=150,562 width=54) (actual time=13.380..62.269 rows=151,655 loops=1)

  • Recheck Cond: ((site_id_)::text = 'VA2301'::text)
  • Heap Blocks: exact=6449
14. 12.368 12.368 ↓ 1.0 151,655 1

Bitmap Index Scan on seria_pro_ide_idx99 (cost=0.00..1,959.64 rows=150,562 width=0) (actual time=12.368..12.368 rows=151,655 loops=1)

  • Index Cond: ((site_id_)::text = 'VA2301'::text)
15. 14.616 88.920 ↓ 65.7 14,250 1

Hash (cost=1,107.13..1,107.13 rows=217 width=543) (actual time=88.920..88.920 rows=14,250 loops=1)

  • Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 4097kB
16. 8.976 74.304 ↓ 65.7 14,250 1

Nested Loop (cost=0.84..1,107.13 rows=217 width=543) (actual time=0.120..74.304 rows=14,250 loops=1)

17. 3.192 23.472 ↓ 19.0 6,976 1

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

18. 4.062 4.062 ↓ 24.7 1,802 1

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

19. 16.218 16.218 ↑ 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.007..0.009 rows=4 loops=1,802)

  • Index Cond: ((delivery_order_id_)::text = (temp.delivery_order_id_)::text)
20. 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))
21. 71.245 71.245 ↑ 1.0 1 14,249

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=14,249)

  • Index Cond: ((delivery_order_item_id_)::text = (doi.delivery_order_item_id_)::text)
  • Filter: ((site_id_)::text = 'VA2301'::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.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. 13.142 31.644 ↑ 1.0 34,969 1

Hash (cost=1,432.44..1,432.44 rows=35,026 width=70) (actual time=31.644..31.644 rows=34,969 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 3502kB
28. 18.502 18.502 ↑ 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.008..18.502 rows=34,969 loops=1)

  • Filter: ((order_role_type_id_)::text = 'C026SHIPMENTPIC'::text)
  • Rows Removed by Filter: 462
29. 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.002..0.002 rows=1 loops=14,249)

  • Index Cond: ((party_id_)::text = (dr.party_id_)::text)
Planning time : 4.817 ms
Execution time : 759.493 ms