explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rDXJ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.752 5,481.079 ↑ 1.0 1,300 1

Unique (cost=52,537.84..52,544.34 rows=1,300 width=516) (actual time=5,478.901..5,481.079 rows=1,300 loops=1)

2.          

CTE missions

3. 16.375 194.942 ↓ 1.2 10,258 1

Unique (cost=27,874.39..28,254.59 rows=8,666 width=569) (actual time=143.041..194.942 rows=10,258 loops=1)

4. 26.445 178.567 ↓ 1.3 11,146 1

Merge Left Join (cost=27,874.39..28,232.92 rows=8,666 width=569) (actual time=143.034..178.567 rows=11,146 loops=1)

  • Merge Cond: (m.id = fr.order_id)
5. 34.790 148.992 ↓ 1.2 10,258 1

Sort (cost=27,653.41..27,675.08 rows=8,666 width=487) (actual time=140.832..148.992 rows=10,258 loops=1)

  • Sort Key: m.id
  • Sort Method: external merge Disk: 4800kB
6. 6.255 114.202 ↓ 1.2 10,258 1

Hash Left Join (cost=21,798.88..25,189.60 rows=8,666 width=487) (actual time=86.132..114.202 rows=10,258 loops=1)

  • Hash Cond: (m.courier_id = c.id)
7. 8.652 107.365 ↓ 1.2 10,258 1

Hash Join (cost=21,754.51..25,091.04 rows=8,666 width=472) (actual time=85.528..107.365 rows=10,258 loops=1)

  • Hash Cond: (m.delivery_id = s.id)
8. 13.258 13.258 ↑ 1.0 12,999 1

Seq Scan on missions m (cost=0.00..3,201.12 rows=13,000 width=472) (actual time=0.019..13.258 rows=12,999 loops=1)

  • Filter: (courier_status <> ALL ('{POSTPONED_UNREACHABLE_CUSTOMER,CANCELLED,DROPOFF_CONFIRMED,POSTPONED_OTHER}'::type_of_courier_statuses[]))
  • Rows Removed by Filter: 2272
9. 2.025 85.455 ↓ 1.2 5,699 1

Hash (cost=21,695.00..21,695.00 rows=4,761 width=16) (actual time=85.455..85.455 rows=5,699 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 332kB
10. 3.181 83.430 ↓ 1.2 5,699 1

Hash Join (cost=3,469.13..21,695.00 rows=4,761 width=16) (actual time=0.270..83.430 rows=5,699 loops=1)

  • Hash Cond: (s.sender_store_id = vs.id)
11. 55.964 80.013 ↓ 1.2 5,699 1

Seq Scan on orders_stores s (cost=3,441.08..21,601.49 rows=4,761 width=32) (actual time=0.017..80.013 rows=5,699 loops=1)

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

SubPlan (for Seq Scan)

13. 24.049 24.049 ↓ 2.4 10,240 1

Seq Scan on missions mls (cost=0.00..3,430.25 rows=4,333 width=16) (actual time=0.015..24.049 rows=10,240 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: 5031
14. 0.112 0.236 ↑ 1.0 397 1

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

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

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

16. 0.194 0.582 ↑ 1.0 461 1

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

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

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

18. 1.973 3.130 ↑ 1.0 2,119 1

Sort (cost=220.98..226.30 rows=2,129 width=17) (actual time=2.170..3.130 rows=2,119 loops=1)

  • Sort Key: fr.order_id
  • Sort Method: quicksort Memory: 262kB
19. 1.157 1.157 ↑ 1.0 2,119 1

Seq Scan on fixed_routes fr (cost=0.00..103.29 rows=2,129 width=17) (actual time=0.026..1.157 rows=2,119 loops=1)

20.          

CTE json_deliv

21. 390.306 604.166 ↓ 28.4 5,688 1

HashAggregate (cost=238.31..240.81 rows=200 width=48) (actual time=596.146..604.166 rows=5,688 loops=1)

  • Group Key: m_1.delivery_id
22. 213.860 213.860 ↓ 1.2 10,258 1

CTE Scan on missions m_1 (cost=0.00..173.32 rows=8,666 width=56) (actual time=143.078..213.860 rows=10,258 loops=1)

23. 18.582 5,480.327 ↑ 1.0 1,300 1

Sort (cost=24,042.43..24,045.68 rows=1,300 width=516) (actual time=5,478.899..5,480.327 rows=1,300 loops=1)

  • Sort Key: orders_stores.id
  • Sort Method: external merge Disk: 4352kB
24. 11.967 5,461.745 ↑ 1.0 1,300 1

Hash Join (cost=28.05..23,975.19 rows=1,300 width=516) (actual time=633.200..5,461.745 rows=1,300 loops=1)

  • Hash Cond: (orders_stores.sender_store_id = vendor_stores.id)
25. 19.233 19.233 ↑ 1.0 1,300 1

Seq Scan on orders_stores (cost=0.00..18,053.28 rows=1,300 width=217) (actual time=0.018..19.233 rows=1,300 loops=1)

  • Filter: (courier_status = 'WAITING'::type_of_courier_statuses)
  • Rows Removed by Filter: 5839
26. 0.191 0.445 ↑ 1.0 397 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 64kB
27. 0.254 0.254 ↑ 1.0 397 1

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

28.          

SubPlan (for Hash Join)

29. 5,430.100 5,430.100 ↑ 1.0 1 1,300

CTE Scan on json_deliv (cost=0.00..4.50 rows=1 width=32) (actual time=2.378..4.177 rows=1 loops=1,300)

  • Filter: (delivery_id = orders_stores.id)
  • Rows Removed by Filter: 5687
Planning time : 5.961 ms
Execution time : 5,488.823 ms