explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K9WJ

Settings
# exclusive inclusive rows x rows loops node
1. 5.278 83.885 ↑ 1.0 5,766 1

Unique (cost=22,349.23..70,412.13 rows=5,766 width=532) (actual time=55.488..83.885 rows=5,766 loops=1)

2. 12.057 78.607 ↑ 1.0 5,766 1

Merge Left Join (cost=22,349.23..70,397.72 rows=5,766 width=532) (actual time=55.486..78.607 rows=5,766 loops=1)

  • Merge Cond: ((s.id = fr.order_id) AND (s.assigned_courier_id = fr.courier_id))
3. 8.978 58.094 ↑ 1.0 5,766 1

Sort (cost=22,111.56..22,125.98 rows=5,766 width=365) (actual time=55.457..58.094 rows=5,766 loops=1)

  • Sort Key: s.id, s.assigned_courier_id
  • Sort Method: quicksort Memory: 3159kB
4. 3.271 49.116 ↑ 1.0 5,766 1

Hash Left Join (cost=3,500.80..21,751.38 rows=5,766 width=365) (actual time=18.993..49.116 rows=5,766 loops=1)

  • Hash Cond: (s.assigned_courier_id = cour.id)
5. 3.665 45.323 ↑ 1.0 5,766 1

Hash Join (cost=3,328.74..21,535.93 rows=5,766 width=341) (actual time=18.459..45.323 rows=5,766 loops=1)

  • Hash Cond: (s.sender_store_id = vs.id)
6. 4.087 41.261 ↑ 1.0 5,766 1

Hash Join (cost=3,300.70..21,428.60 rows=5,766 width=232) (actual time=18.054..41.261 rows=5,766 loops=1)

  • Hash Cond: (s.id = missions.delivery_id)
7. 19.151 19.151 ↓ 1.0 5,874 1

Seq Scan on orders_stores s (cost=0.00..18,062.96 rows=5,871 width=232) (actual time=0.006..19.151 rows=5,874 loops=1)

  • Filter: (courier_status <> ALL ('{DROPOFF_CONFIRMED,CANCELLED,POSTPONED_OTHER}'::type_of_courier_statuses[]))
  • Rows Removed by Filter: 1326
8. 2.288 18.023 ↓ 1.3 7,068 1

Hash (cost=3,234.90..3,234.90 rows=5,264 width=16) (actual time=18.023..18.023 rows=7,068 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 396kB
9. 4.953 15.735 ↓ 1.3 7,068 1

HashAggregate (cost=3,182.26..3,234.90 rows=5,264 width=16) (actual time=13.732..15.735 rows=7,068 loops=1)

  • Group Key: missions.delivery_id
10. 10.782 10.782 ↑ 1.0 7,068 1

Seq Scan on missions (cost=0.00..3,164.59 rows=7,068 width=16) (actual time=0.005..10.782 rows=7,068 loops=1)

  • Filter: (type_of_mission = 'PICKUP_TO_AIRMEE_WAREHOUSE'::types_of_missions)
  • Rows Removed by Filter: 8339
11. 0.171 0.397 ↑ 1.0 397 1

Hash (cost=23.02..23.02 rows=402 width=141) (actual time=0.397..0.397 rows=397 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 66kB
12. 0.226 0.226 ↑ 1.0 397 1

Seq Scan on vendor_stores vs (cost=0.00..23.02 rows=402 width=141) (actual time=0.008..0.226 rows=397 loops=1)

13. 0.159 0.522 ↑ 1.0 447 1

Hash (cost=166.47..166.47 rows=447 width=40) (actual time=0.522..0.522 rows=447 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
14. 0.363 0.363 ↑ 1.0 447 1

Seq Scan on couriers_active cour (cost=0.00..166.47 rows=447 width=40) (actual time=0.011..0.363 rows=447 loops=1)

15. 1.841 2.690 ↑ 1.0 2,328 1

Sort (cost=237.67..243.56 rows=2,358 width=44) (actual time=2.037..2.690 rows=2,328 loops=1)

  • Sort Key: fr.order_id, fr.courier_id
  • Sort Method: quicksort Memory: 279kB
16. 0.849 0.849 ↑ 1.0 2,330 1

Seq Scan on fixed_routes fr (cost=0.00..105.58 rows=2,358 width=44) (actual time=0.009..0.849 rows=2,330 loops=1)

17.          

SubPlan (for Merge Left Join)

18. 5.766 5.766 ↓ 0.0 0 5,766

Index Scan using admin_couriers_id on couriers c (cost=0.27..8.30 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=5,766)

  • Index Cond: (id = s.assigned_courier_id)
Planning time : 2.451 ms
Execution time : 85.145 ms