explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8xNN : 3

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

Sort (cost=3,856.86..3,856.86 rows=3 width=409) (actual time=612.949..617.100 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.704 3.704 ↓ 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.035..3.704 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.988 564.441 ↓ 4,749.7 14,249 1

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

5. 8.326 534.955 ↓ 4,749.7 14,249 1

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

6. 30.068 512.380 ↓ 4,749.7 14,249 1

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

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

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

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

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

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

10. 15.140 405.117 ↓ 1,583.2 14,249 1

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

11. 10.036 361.479 ↓ 1,583.2 14,249 1

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

12. 7.770 322.945 ↓ 1,583.2 14,249 1

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

13. 17.779 258.179 ↓ 1,583.2 14,249 1

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

14. 6.696 140.650 ↓ 233.6 14,250 1

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

15. 9.587 78.146 ↓ 67.7 6,976 1

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

16. 4.552 26.703 ↓ 19.0 6,976 1

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

17. 5.933 5.933 ↓ 24.7 1,802 1

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

18. 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)
19. 41.856 41.856 ↑ 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.006 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. 99.750 99.750 ↑ 1.0 1 14,250

Index Scan using seria_pro_ide_idx99 on serialized_product_identifier spi (cost=0.43..1.30 rows=1 width=54) (actual time=0.007..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. 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.003..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 : 4.889 ms
Execution time : 618.881 ms