explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mKXg

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 4,932.481 ↓ 1.7 5 1

Append (cost=32,287.00..32,287.52 rows=3 width=176) (actual time=4,728.486..4,932.481 rows=5 loops=1)

2.          

CTE user_areas

3. 2,741.496 2,741.852 ↓ 1.5 3 1

GroupAggregate (cost=4.27..18.31 rows=2 width=90) (actual time=62.796..2,741.852 rows=3 loops=1)

  • Group Key: reg.id
4. 0.086 0.356 ↓ 3.2 29 1

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

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

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

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

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

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

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

8. 0.010 0.026 ↑ 1.0 3 1

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

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

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

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

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

11. 0.021 0.111 ↓ 4.8 29 1

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

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

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

13. 0.027 0.042 ↓ 4.8 29 1

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

  • Sort Key: areas_mapping.area_id
  • Sort Method: quicksort Memory: 27kB
14. 0.015 0.015 ↓ 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.005..0.015 rows=29 loops=1)

15.          

CTE orders_info

16. 114.399 4,521.092 ↓ 2.0 4 1

GroupAggregate (cost=18,577.29..18,908.93 rows=2 width=160) (actual time=4,317.304..4,521.092 rows=4 loops=1)

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

Initplan (for GroupAggregate)

18. 0.053 0.094 ↑ 1.0 1 1

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

19. 0.041 0.041 ↑ 1.0 7 1

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

  • Filter: default_area
  • Rows Removed by Filter: 27
20. 0.027 0.037 ↑ 1.0 1 1

Aggregate (cost=13.36..13.37 rows=1 width=65) (actual time=0.037..0.037 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.002..0.010 rows=7 loops=1)

  • Filter: default_area
  • Rows Removed by Filter: 27
22. 0.025 0.033 ↑ 1.0 1 1

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

23. 0.008 0.008 ↑ 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.008 rows=7 loops=1)

  • Filter: default_area
  • Rows Removed by Filter: 27
24. 0.024 0.033 ↑ 1.0 1 1

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

25. 0.009 0.009 ↑ 1.0 7 1

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

  • Filter: default_area
  • Rows Removed by Filter: 27
26. 1,253.649 4,406.496 ↑ 1.0 4,144 1

Sort (cost=18,523.81..18,534.17 rows=4,144 width=116) (actual time=4,296.017..4,406.496 rows=4,144 loops=1)

  • Sort Key: ua.region_id, ua.region_name
  • Sort Method: external merge Disk: 88024kB
27. 114.364 3,152.847 ↑ 1.0 4,144 1

Nested Loop Left Join (cost=0.00..18,274.82 rows=4,144 width=116) (actual time=2,685.255..3,152.847 rows=4,144 loops=1)

  • Join Filter: (del_2.area_id = ANY (ua.area_ids))
  • Rows Removed by Join Filter: 8297
28. 158.403 158.403 ↑ 1.0 4,144 1

Seq Scan on orders_stores del_2 (cost=0.00..18,005.44 rows=4,144 width=40) (actual time=0.009..158.403 rows=4,144 loops=1)

29. 2,880.080 2,880.080 ↓ 1.5 3 4,144

CTE Scan on user_areas ua (cost=0.00..0.04 rows=2 width=112) (actual time=0.022..0.695 rows=3 loops=4,144)

30.          

CTE today_missions_info

31. 17.777 411.080 ↓ 2.0 4 1

GroupAggregate (cost=13,310.85..13,359.70 rows=2 width=112) (actual time=392.685..411.080 rows=4 loops=1)

  • Group Key: ua_1.region_id, ua_1.region_name
32. 3.632 393.303 ↓ 1.7 2,208 1

Sort (cost=13,310.85..13,314.11 rows=1,302 width=551) (actual time=392.493..393.303 rows=2,208 loops=1)

  • Sort Key: ua_1.region_id, ua_1.region_name
  • Sort Method: quicksort Memory: 2213kB
33. 4.288 389.671 ↓ 1.7 2,208 1

Nested Loop Left Join (cost=0.41..13,243.50 rows=1,302 width=551) (actual time=0.123..389.671 rows=2,208 loops=1)

  • Join Filter: (mis_2.area_id = ANY (ua_1.area_ids))
  • Rows Removed by Join Filter: 4645
34. 13.593 336.807 ↓ 1.7 2,208 1

Nested Loop (cost=0.41..13,158.85 rows=1,302 width=507) (actual time=0.103..336.807 rows=2,208 loops=1)

35. 154.050 154.050 ↓ 2.4 4,699 1

Seq Scan on missions mis_2 (cost=0.00..3,259.53 rows=1,967 width=523) (actual time=0.042..154.050 rows=4,699 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: 4502
36. 169.164 169.164 ↓ 0.0 0 4,699

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

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

CTE Scan on user_areas ua_1 (cost=0.00..0.04 rows=2 width=80) (actual time=0.010..0.022 rows=3 loops=2,208)

38. 0.183 4,932.392 ↓ 2.0 4 1

Hash Join (cost=0.07..0.16 rows=2 width=176) (actual time=4,728.484..4,932.392 rows=4 loops=1)

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

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

40. 0.010 411.100 ↓ 2.0 4 1

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

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

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

42. 0.050 0.083 ↑ 1.0 1 1

Aggregate (cost=0.37..0.39 rows=1 width=176) (actual time=0.083..0.083 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.001 0.001 ↑ 1.0 1 1

CTE Scan on orders_info orders_info_1 (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
46. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on orders_info orders_info_2 (cost=0.00..0.04 rows=1 width=8) (actual time=0.002..0.003 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.015 0.024 ↓ 2.0 4 1

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

  • Hash Cond: (del_1.region_name = mis_1.region_name)
49. 0.001 0.001 ↓ 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.001 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.002..0.006 rows=4 loops=1)

Planning time : 2.297 ms
Execution time : 4,984.916 ms