explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2VzU

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 491.716 ↓ 1.7 5 1

Append (cost=32,253.92..32,254.44 rows=3 width=176) (actual time=393.465..491.716 rows=5 loops=1)

2.          

CTE user_areas

3. 159.209 159.513 ↓ 1.5 3 1

GroupAggregate (cost=4.27..18.31 rows=2 width=90) (actual time=6.863..159.513 rows=3 loops=1)

  • Group Key: reg.id
4. 0.080 0.304 ↓ 3.2 29 1

Nested Loop (cost=4.27..15.96 rows=9 width=4,233) (actual time=0.096..0.304 rows=29 loops=1)

  • Join Filter: (areas_mapping.region_id = reg.id)
  • Rows Removed by Join Filter: 58
5. 0.008 0.047 ↑ 1.0 3 1

Merge Join (cost=3.00..3.04 rows=3 width=42) (actual time=0.034..0.047 rows=3 loops=1)

  • Merge Cond: (reg.id = user_mapping.region_id)
6. 0.013 0.017 ↓ 1.5 3 1

Sort (cost=1.03..1.03 rows=2 width=26) (actual time=0.013..0.017 rows=3 loops=1)

  • Sort Key: reg.id
  • Sort Method: quicksort Memory: 25kB
7. 0.004 0.004 ↓ 1.5 3 1

Seq Scan on regions reg (cost=0.00..1.02 rows=2 width=26) (actual time=0.003..0.004 rows=3 loops=1)

8. 0.009 0.022 ↑ 1.0 3 1

Sort (cost=1.96..1.97 rows=3 width=16) (actual time=0.018..0.022 rows=3 loops=1)

  • Sort Key: user_mapping.region_id
  • Sort Method: quicksort Memory: 25kB
9. 0.013 0.013 ↑ 1.0 3 1

Seq Scan on dashboard_user_responsibility user_mapping (cost=0.00..1.94 rows=3 width=16) (actual time=0.007..0.013 rows=3 loops=1)

  • Filter: (user_id = 'ef1419e6-b4e4-11e8-859d-df117af2d800'::uuid)
  • Rows Removed by Filter: 81
10. 0.071 0.177 ↓ 4.8 29 3

Materialize (cost=1.28..12.67 rows=6 width=4,223) (actual time=0.011..0.059 rows=29 loops=3)

11. 0.033 0.106 ↓ 4.8 29 1

Merge Join (cost=1.28..12.64 rows=6 width=4,223) (actual time=0.029..0.106 rows=29 loops=1)

  • Merge Cond: (areas.id = areas_mapping.area_id)
12. 0.041 0.041 ↑ 1.0 34 1

Index Scan using areas_pkey on areas (cost=0.14..38.37 rows=34 width=4,207) (actual time=0.004..0.041 rows=34 loops=1)

13. 0.019 0.032 ↓ 4.8 29 1

Sort (cost=1.14..1.15 rows=6 width=20) (actual time=0.024..0.032 rows=29 loops=1)

  • Sort Key: areas_mapping.area_id
  • Sort Method: quicksort Memory: 27kB
14. 0.013 0.013 ↓ 4.8 29 1

Seq Scan on areas_to_regions_mapping areas_mapping (cost=0.00..1.06 rows=6 width=20) (actual time=0.004..0.013 rows=29 loops=1)

15.          

CTE orders_info

16. 58.445 439.512 ↓ 2.0 4 1

GroupAggregate (cost=18,576.29..18,907.37 rows=2 width=160) (actual time=341.475..439.512 rows=4 loops=1)

  • Group Key: ua.region_id, ua.region_name
17.          

Initplan (for GroupAggregate)

18. 0.054 0.092 ↑ 1.0 1 1

Aggregate (cost=13.36..13.37 rows=1 width=65) (actual time=0.092..0.092 rows=1 loops=1)

19. 0.038 0.038 ↑ 1.0 7 1

Seq Scan on areas areas_1 (cost=0.00..13.34 rows=7 width=4,203) (actual time=0.011..0.038 rows=7 loops=1)

  • Filter: default_area
  • Rows Removed by Filter: 27
20. 0.025 0.035 ↑ 1.0 1 1

Aggregate (cost=13.36..13.37 rows=1 width=65) (actual time=0.035..0.035 rows=1 loops=1)

21. 0.010 0.010 ↑ 1.0 7 1

Seq Scan on areas areas_2 (cost=0.00..13.34 rows=7 width=4,203) (actual time=0.003..0.010 rows=7 loops=1)

  • Filter: default_area
  • Rows Removed by Filter: 27
22. 0.028 0.035 ↑ 1.0 1 1

Aggregate (cost=13.36..13.37 rows=1 width=65) (actual time=0.034..0.035 rows=1 loops=1)

23. 0.007 0.007 ↑ 1.0 7 1

Seq Scan on areas areas_3 (cost=0.00..13.34 rows=7 width=4,203) (actual time=0.001..0.007 rows=7 loops=1)

  • Filter: default_area
  • Rows Removed by Filter: 27
24. 0.030 0.037 ↑ 1.0 1 1

Aggregate (cost=13.36..13.37 rows=1 width=65) (actual time=0.036..0.037 rows=1 loops=1)

25. 0.007 0.007 ↑ 1.0 7 1

Seq Scan on areas areas_4 (cost=0.00..13.34 rows=7 width=4,203) (actual time=0.001..0.007 rows=7 loops=1)

  • Filter: default_area
  • Rows Removed by Filter: 27
26. 176.447 380.868 ↑ 1.0 4,137 1

Sort (cost=18,522.81..18,533.15 rows=4,137 width=116) (actual time=336.593..380.868 rows=4,137 loops=1)

  • Sort Key: ua.region_id, ua.region_name
  • Sort Method: external merge Disk: 87824kB
27. 9.837 204.421 ↑ 1.0 4,137 1

Nested Loop Left Join (cost=0.00..18,274.30 rows=4,137 width=116) (actual time=155.460..204.421 rows=4,137 loops=1)

  • Join Filter: (del_2.area_id = ANY (ua.area_ids))
  • Rows Removed by Join Filter: 8283
28. 16.693 16.693 ↑ 1.0 4,137 1

Seq Scan on orders_stores del_2 (cost=0.00..18,005.37 rows=4,137 width=40) (actual time=0.007..16.693 rows=4,137 loops=1)

29. 177.891 177.891 ↓ 1.5 3 4,137

CTE Scan on user_areas ua (cost=0.00..0.04 rows=2 width=112) (actual time=0.003..0.043 rows=3 loops=4,137)

30.          

CTE today_missions_info

31. 1.661 51.905 ↓ 2.0 4 1

GroupAggregate (cost=13,279.41..13,328.18 rows=2 width=112) (actual time=49.689..51.905 rows=4 loops=1)

  • Group Key: ua_1.region_id, ua_1.region_name
32. 3.163 50.244 ↓ 1.7 2,224 1

Sort (cost=13,279.41..13,282.66 rows=1,300 width=551) (actual time=49.499..50.244 rows=2,224 loops=1)

  • Sort Key: ua_1.region_id, ua_1.region_name
  • Sort Method: quicksort Memory: 2226kB
33. 3.538 47.081 ↓ 1.7 2,224 1

Nested Loop Left Join (cost=0.41..13,212.17 rows=1,300 width=551) (actual time=0.102..47.081 rows=2,224 loops=1)

  • Join Filter: (mis_2.area_id = ANY (ua_1.area_ids))
  • Rows Removed by Join Filter: 4681
34. 6.305 34.647 ↓ 1.7 2,224 1

Nested Loop (cost=0.41..13,127.65 rows=1,300 width=507) (actual time=0.089..34.647 rows=2,224 loops=1)

35. 14.236 14.236 ↓ 2.4 4,702 1

Seq Scan on missions mis_2 (cost=0.00..3,259.12 rows=1,962 width=523) (actual time=0.035..14.236 rows=4,702 loops=1)

  • Filter: ((courier_status <> ALL ('{DROPOFF_CONFIRMED,POSTPONED_UNREACHABLE_CUSTOMER,POSTPONED_OTHER,WAITING,CANCELLED}'::type_of_courier_statuses[])) AND ((LEAST(pickup_earliest_time, dropoff_latest_time))::double precision <= date_part('epoch'::text, (('now'::cstring)::date + '1 day'::interval))))
  • Rows Removed by Filter: 4486
36. 14.106 14.106 ↓ 0.0 0 4,702

Index Scan using delivery_id_index on orders_stores del_3 (cost=0.41..5.02 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=4,702)

  • Index Cond: (id = mis_2.delivery_id)
  • Filter: (courier_status <> ALL ('{POSTPONED_OTHER,WAITING}'::type_of_courier_statuses[]))
  • Rows Removed by Filter: 1
37. 8.896 8.896 ↓ 1.5 3 2,224

CTE Scan on user_areas ua_1 (cost=0.00..0.04 rows=2 width=80) (actual time=0.001..0.004 rows=3 loops=2,224)

38. 0.176 491.622 ↓ 2.0 4 1

Hash Join (cost=0.07..0.16 rows=2 width=176) (actual time=393.463..491.622 rows=4 loops=1)

  • Hash Cond: (del.region_name = mis.region_name)
39. 439.525 439.525 ↓ 2.0 4 1

CTE Scan on orders_info del (cost=0.00..0.04 rows=2 width=128) (actual time=341.479..439.525 rows=4 loops=1)

40. 0.006 51.921 ↓ 2.0 4 1

Hash (cost=0.04..0.04 rows=2 width=64) (actual time=51.921..51.921 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 51.915 51.915 ↓ 2.0 4 1

CTE Scan on today_missions_info mis (cost=0.00..0.04 rows=2 width=64) (actual time=49.693..51.915 rows=4 loops=1)

42. 0.053 0.086 ↑ 1.0 1 1

Aggregate (cost=0.37..0.39 rows=1 width=176) (actual time=0.086..0.086 rows=1 loops=1)

43.          

Initplan (for Aggregate)

44. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on orders_info (cost=0.00..0.04 rows=1 width=8) (actual time=0.002..0.004 rows=1 loops=1)

  • Filter: (region_id IS NULL)
  • Rows Removed by Filter: 3
45. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on orders_info orders_info_1 (cost=0.00..0.04 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: (region_id IS NULL)
  • Rows Removed by Filter: 3
46. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on orders_info orders_info_2 (cost=0.00..0.04 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

  • Filter: (region_id IS NULL)
  • Rows Removed by Filter: 3
47. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on orders_info orders_info_3 (cost=0.00..0.04 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

  • Filter: (region_id IS NULL)
  • Rows Removed by Filter: 3
48. 0.013 0.025 ↓ 2.0 4 1

Hash Join (cost=0.07..0.13 rows=2 width=80) (actual time=0.021..0.025 rows=4 loops=1)

  • Hash Cond: (del_1.region_name = mis_1.region_name)
49. 0.004 0.004 ↓ 2.0 4 1

CTE Scan on orders_info del_1 (cost=0.00..0.04 rows=2 width=80) (actual time=0.001..0.004 rows=4 loops=1)

50. 0.002 0.008 ↓ 2.0 4 1

Hash (cost=0.04..0.04 rows=2 width=64) (actual time=0.008..0.008 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.006 0.006 ↓ 2.0 4 1

CTE Scan on today_missions_info mis_1 (cost=0.00..0.04 rows=2 width=64) (actual time=0.001..0.006 rows=4 loops=1)

Planning time : 2.227 ms
Execution time : 507.716 ms