explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IXOv : Optimization for: missing; plan #aii9

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 6.530 522.980 ↑ 1.0 5,737 1

Unique (cost=48,129.74..96,829.55 rows=5,846 width=532) (actual time=484.480..522.980 rows=5,737 loops=1)

2.          

CTE missions

3. 9.790 90.013 ↑ 1.6 7,050 1

Unique (cost=25,130.32..25,316.27 rows=11,041 width=569) (actual time=64.265..90.013 rows=7,050 loops=1)

4. 10.240 80.223 ↑ 1.5 7,224 1

Merge Left Join (cost=25,130.32..25,288.66 rows=11,041 width=569) (actual time=64.259..80.223 rows=7,224 loops=1)

  • Merge Cond: (m.id = fr_1.order_id)
5. 22.199 67.376 ↑ 1.6 7,050 1

Sort (cost=24,909.99..24,937.60 rows=11,041 width=487) (actual time=62.445..67.376 rows=7,050 loops=1)

  • Sort Key: m.id
  • Sort Method: external merge Disk: 3224kB
6. 4.364 45.177 ↑ 1.6 7,050 1

Hash Left Join (cost=18,296.80..21,750.06 rows=11,041 width=487) (actual time=23.809..45.177 rows=7,050 loops=1)

  • Hash Cond: (m.courier_id = c.id)
7. 5.714 40.385 ↑ 1.6 7,050 1

Hash Join (cost=18,252.43..21,637.14 rows=11,041 width=472) (actual time=23.359..40.385 rows=7,050 loops=1)

  • Hash Cond: (m.delivery_id = s_1.id)
8. 11.356 11.356 ↑ 1.6 7,050 1

Seq Scan on missions m (cost=0.00..3,272.80 rows=11,041 width=472) (actual time=0.013..11.356 rows=7,050 loops=1)

  • Filter: (type_of_mission = 'PICKUP_TO_AIRMEE_WAREHOUSE'::types_of_missions)
  • Rows Removed by Filter: 8316
9. 2.386 23.315 ↑ 1.0 7,183 1

Hash (cost=18,162.64..18,162.64 rows=7,183 width=16) (actual time=23.314..23.315 rows=7,183 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 401kB
10. 4.392 20.929 ↑ 1.0 7,183 1

Hash Join (cost=28.05..18,162.64 rows=7,183 width=16) (actual time=0.293..20.929 rows=7,183 loops=1)

  • Hash Cond: (s_1.sender_store_id = vs_1.id)
11. 16.263 16.263 ↑ 1.0 7,183 1

Seq Scan on orders_stores s_1 (cost=0.00..18,035.83 rows=7,183 width=32) (actual time=0.003..16.263 rows=7,183 loops=1)

12. 0.128 0.274 ↑ 1.0 397 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
13. 0.146 0.146 ↑ 1.0 397 1

Seq Scan on vendor_stores vs_1 (cost=0.00..23.02 rows=402 width=16) (actual time=0.004..0.146 rows=397 loops=1)

14. 0.190 0.428 ↑ 1.0 461 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
15. 0.238 0.238 ↑ 1.0 461 1

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

16. 1.801 2.607 ↓ 1.0 2,122 1

Sort (cost=220.33..225.63 rows=2,120 width=17) (actual time=1.795..2.607 rows=2,122 loops=1)

  • Sort Key: fr_1.order_id
  • Sort Method: quicksort Memory: 262kB
17. 0.806 0.806 ↓ 1.0 2,122 1

Seq Scan on fixed_routes fr_1 (cost=0.00..103.20 rows=2,120 width=17) (actual time=0.014..0.806 rows=2,122 loops=1)

18.          

CTE json_deliv

19. 258.437 361.136 ↓ 35.2 7,050 1

HashAggregate (cost=303.63..306.13 rows=200 width=48) (actual time=353.084..361.136 rows=7,050 loops=1)

  • Group Key: m_1.delivery_id
20. 102.699 102.699 ↑ 1.6 7,050 1

CTE Scan on missions m_1 (cost=0.00..220.82 rows=11,041 width=56) (actual time=64.284..102.699 rows=7,050 loops=1)

21. 15.702 516.450 ↑ 1.0 5,737 1

Merge Left Join (cost=22,507.34..71,192.54 rows=5,846 width=532) (actual time=484.476..516.450 rows=5,737 loops=1)

  • Merge Cond: (s.id = json_deliv.delivery_id)
22. 9.417 65.555 ↑ 1.0 5,737 1

Sort (cost=22,495.70..22,510.32 rows=5,846 width=377) (actual time=62.531..65.555 rows=5,737 loops=1)

  • Sort Key: s.id
  • Sort Method: quicksort Memory: 3142kB
23. 3.744 56.138 ↑ 1.0 5,737 1

Hash Left Join (cost=3,820.55..22,129.94 rows=5,846 width=377) (actual time=21.239..56.138 rows=5,737 loops=1)

  • Hash Cond: ((s.id = fr.order_id) AND (s.assigned_courier_id = fr.courier_id))
24. 3.401 50.766 ↑ 1.0 5,737 1

Hash Left Join (cost=3,685.55..21,936.40 rows=5,846 width=365) (actual time=19.586..50.766 rows=5,737 loops=1)

  • Hash Cond: (s.assigned_courier_id = cour.id)
25. 4.107 46.840 ↑ 1.0 5,737 1

Hash Join (cost=3,513.49..21,721.30 rows=5,846 width=341) (actual time=19.043..46.840 rows=5,737 loops=1)

  • Hash Cond: (s.id = missions.delivery_id)
26. 4.261 24.207 ↑ 1.0 5,846 1

Hash Join (cost=28.05..18,171.19 rows=5,846 width=341) (actual time=0.439..24.207 rows=5,846 loops=1)

  • Hash Cond: (s.sender_store_id = vs.id)
27. 19.535 19.535 ↑ 1.0 5,846 1

Seq Scan on orders_stores s (cost=0.00..18,062.77 rows=5,846 width=232) (actual time=0.010..19.535 rows=5,846 loops=1)

  • Filter: (courier_status <> ALL ('{DROPOFF_CONFIRMED,CANCELLED,POSTPONED_OTHER}'::type_of_courier_statuses[]))
  • Rows Removed by Filter: 1337
28. 0.196 0.411 ↑ 1.0 397 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 66kB
29. 0.215 0.215 ↑ 1.0 397 1

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

30. 2.429 18.526 ↑ 1.2 7,050 1

Hash (cost=3,382.64..3,382.64 rows=8,224 width=16) (actual time=18.526..18.526 rows=7,050 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 459kB
31. 4.988 16.097 ↑ 1.2 7,050 1

HashAggregate (cost=3,300.40..3,382.64 rows=8,224 width=16) (actual time=13.914..16.097 rows=7,050 loops=1)

  • Group Key: missions.delivery_id
32. 11.109 11.109 ↑ 1.6 7,050 1

Seq Scan on missions (cost=0.00..3,272.80 rows=11,041 width=16) (actual time=0.008..11.109 rows=7,050 loops=1)

  • Filter: (type_of_mission = 'PICKUP_TO_AIRMEE_WAREHOUSE'::types_of_missions)
  • Rows Removed by Filter: 8316
33. 0.171 0.525 ↑ 1.0 447 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
34. 0.354 0.354 ↑ 1.0 447 1

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

35. 0.823 1.628 ↓ 1.0 2,122 1

Hash (cost=103.20..103.20 rows=2,120 width=44) (actual time=1.628..1.628 rows=2,122 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 198kB
36. 0.805 0.805 ↓ 1.0 2,122 1

Seq Scan on fixed_routes fr (cost=0.00..103.20 rows=2,120 width=44) (actual time=0.006..0.805 rows=2,122 loops=1)

37. 44.356 429.456 ↓ 35.2 7,050 1

Sort (cost=11.64..12.14 rows=200 width=48) (actual time=421.909..429.456 rows=7,050 loops=1)

  • Sort Key: json_deliv.delivery_id
  • Sort Method: external sort Disk: 11536kB
38. 385.100 385.100 ↓ 35.2 7,050 1

CTE Scan on json_deliv (cost=0.00..4.00 rows=200 width=48) (actual time=353.091..385.100 rows=7,050 loops=1)

39.          

SubPlan (for Merge Left Join)

40. 5.737 5.737 ↓ 0.0 0 5,737

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

  • Index Cond: (id = s.assigned_courier_id)
Planning time : 3.786 ms
Execution time : 531.188 ms