explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2ECN : Optimization for: plan #rDXJ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.517 702.569 ↑ 1.9 1,299 1

Unique (cost=47,141.38..47,210.72 rows=2,472 width=516) (actual time=689.100..702.569 rows=1,299 loops=1)

2.          

CTE missions

3. 15.789 173.962 ↓ 1.2 10,261 1

Unique (cost=28,170.93..28,552.51 rows=8,701 width=569) (actual time=123.868..173.962 rows=10,261 loops=1)

4. 25.981 158.173 ↓ 1.3 11,122 1

Merge Left Join (cost=28,170.93..28,530.76 rows=8,701 width=569) (actual time=123.864..158.173 rows=11,122 loops=1)

  • Merge Cond: (m.id = fr.order_id)
5. 31.359 129.583 ↓ 1.2 10,261 1

Sort (cost=27,950.61..27,972.36 rows=8,701 width=487) (actual time=122.114..129.583 rows=10,261 loops=1)

  • Sort Key: m.id
  • Sort Method: external merge Disk: 4800kB
6. 6.346 98.224 ↓ 1.2 10,261 1

Hash Left Join (cost=21,965.92..25,477.26 rows=8,701 width=487) (actual time=63.675..98.224 rows=10,261 loops=1)

  • Hash Cond: (m.courier_id = c.id)
7. 6.571 91.438 ↓ 1.2 10,261 1

Hash Join (cost=21,921.54..25,378.64 rows=8,701 width=472) (actual time=63.216..91.438 rows=10,261 loops=1)

  • Hash Cond: (s.sender_store_id = vs.id)
8. 8.969 80.572 ↓ 1.2 10,261 1

Hash Join (cost=21,893.50..25,230.95 rows=8,701 width=488) (actual time=58.900..80.572 rows=10,261 loops=1)

  • Hash Cond: (m.delivery_id = s.id)
9. 12.758 12.758 ↓ 1.0 13,056 1

Seq Scan on missions m (cost=0.00..3,201.50 rows=13,052 width=472) (actual time=0.014..12.758 rows=13,056 loops=1)

  • Filter: (courier_status <> ALL ('{POSTPONED_UNREACHABLE_CUSTOMER,CANCELLED,DROPOFF_CONFIRMED,POSTPONED_OTHER}'::type_of_courier_statuses[]))
  • Rows Removed by Filter: 2246
10. 2.366 58.845 ↑ 1.6 5,685 1

Hash (cost=21,780.10..21,780.10 rows=9,072 width=32) (actual time=58.845..58.845 rows=5,685 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 484kB
11. 33.652 56.479 ↑ 1.6 5,685 1

Seq Scan on orders_stores s (cost=3,441.88..21,780.10 rows=9,072 width=32) (actual time=0.022..56.479 rows=5,685 loops=1)

  • Filter: (((to_timestamp((dropoff_earliest_time)::double precision))::date <= ('now'::cstring)::date) OR (hashed SubPlan 1))
  • Rows Removed by Filter: 1467
12.          

SubPlan (for Seq Scan)

13. 22.827 22.827 ↓ 2.4 10,241 1

Seq Scan on missions mls (cost=0.00..3,431.00 rows=4,351 width=16) (actual time=0.009..22.827 rows=10,241 loops=1)

  • Filter: ((courier_status <> ALL ('{POSTPONED_UNREACHABLE_CUSTOMER,CANCELLED,DROPOFF_CONFIRMED,POSTPONED_OTHER}'::type_of_courier_statuses[])) AND ((to_timestamp((dropoff_earliest_time)::double precision))::date <= ('now'::cstring)::date))
  • Rows Removed by Filter: 5061
14. 4.145 4.295 ↑ 1.0 397 1

Hash (cost=23.02..23.02 rows=402 width=16) (actual time=4.294..4.295 rows=397 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
15. 0.150 0.150 ↑ 1.0 397 1

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

16. 0.204 0.440 ↑ 1.0 461 1

Hash (cost=38.61..38.61 rows=461 width=31) (actual time=0.440..0.440 rows=461 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
17. 0.236 0.236 ↑ 1.0 461 1

Seq Scan on couriers c (cost=0.00..38.61 rows=461 width=31) (actual time=0.008..0.236 rows=461 loops=1)

18. 1.836 2.609 ↓ 1.0 2,121 1

Sort (cost=220.33..225.63 rows=2,120 width=17) (actual time=1.716..2.609 rows=2,121 loops=1)

  • Sort Key: fr.order_id
  • Sort Method: quicksort Memory: 262kB
19. 0.773 0.773 ↓ 1.0 2,121 1

Seq Scan on fixed_routes fr (cost=0.00..103.20 rows=2,120 width=17) (actual time=0.011..0.773 rows=2,121 loops=1)

20.          

CTE json_deliv

21. 380.022 573.099 ↓ 28.4 5,674 1

HashAggregate (cost=239.28..241.78 rows=200 width=48) (actual time=564.736..573.099 rows=5,674 loops=1)

  • Group Key: m_1.delivery_id
22. 193.077 193.077 ↓ 1.2 10,261 1

CTE Scan on missions m_1 (cost=0.00..174.02 rows=8,701 width=56) (actual time=123.886..193.077 rows=10,261 loops=1)

23. 3.664 701.052 ↑ 1.9 1,299 1

Merge Left Join (cost=18,347.09..18,410.25 rows=2,472 width=516) (actual time=689.091..701.052 rows=1,299 loops=1)

  • Merge Cond: (orders_stores.id = json_deliv.delivery_id)
24. 5.745 41.652 ↑ 1.9 1,299 1

Sort (cost=18,335.45..18,341.63 rows=2,472 width=292) (actual time=41.028..41.652 rows=1,299 loops=1)

  • Sort Key: orders_stores.id
  • Sort Method: quicksort Memory: 719kB
25. 1.049 35.907 ↑ 1.9 1,299 1

Hash Join (cost=28.05..18,196.13 rows=2,472 width=292) (actual time=0.412..35.907 rows=1,299 loops=1)

  • Hash Cond: (orders_stores.sender_store_id = vendor_stores.id)
26. 34.463 34.463 ↑ 1.9 1,299 1

Seq Scan on orders_stores (cost=0.00..18,134.10 rows=2,472 width=217) (actual time=0.009..34.463 rows=1,299 loops=1)

  • Filter: (courier_status = 'WAITING'::type_of_courier_statuses)
  • Rows Removed by Filter: 5853
27. 0.194 0.395 ↑ 1.0 397 1

Hash (cost=23.02..23.02 rows=402 width=107) (actual time=0.395..0.395 rows=397 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 64kB
28. 0.201 0.201 ↑ 1.0 397 1

Seq Scan on vendor_stores (cost=0.00..23.02 rows=402 width=107) (actual time=0.003..0.201 rows=397 loops=1)

29. 52.691 655.736 ↓ 28.3 5,662 1

Sort (cost=11.64..12.14 rows=200 width=48) (actual time=648.024..655.736 rows=5,662 loops=1)

  • Sort Key: json_deliv.delivery_id
  • Sort Method: external sort Disk: 16704kB
30. 603.045 603.045 ↓ 28.4 5,674 1

CTE Scan on json_deliv (cost=0.00..4.00 rows=200 width=48) (actual time=564.743..603.045 rows=5,674 loops=1)

Planning time : 6.687 ms
Execution time : 712.963 ms