explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0fz

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 5,676.037 ↑ 100.0 1 1

Limit (cost=992.09..1,465.97 rows=100 width=164) (actual time=5,658.601..5,676.037 rows=1 loops=1)

2.          

CTE scheduled

3. 0.169 0.818 ↑ 1.0 304 1

HashAggregate (cost=83.74..84.65 rows=304 width=24) (actual time=0.775..0.818 rows=304 loops=1)

  • Group Key: request_scheduled.request_id, request_scheduled."timestamp", (to_timestamp(((request_scheduled.request_call_scheduled_date / 1000))::double precision))
4. 0.020 0.649 ↑ 1.0 304 1

Append (cost=0.00..83.28 rows=304 width=24) (actual time=0.007..0.649 rows=304 loops=1)

5. 0.503 0.503 ↑ 1.0 254 1

Seq Scan on request_scheduled (cost=0.00..62.14 rows=254 width=24) (actual time=0.007..0.503 rows=254 loops=1)

6. 0.075 0.075 ↓ 1.4 29 1

Seq Scan on request_event_updated_by_users (cost=0.00..10.09 rows=20 width=24) (actual time=0.010..0.075 rows=29 loops=1)

7. 0.051 0.051 ↑ 1.4 21 1

Seq Scan on request_auto_rescheduled (cost=0.00..10.13 rows=30 width=24) (actual time=0.010..0.051 rows=21 loops=1)

8.          

CTE correct_request_event_declined

9. 0.072 4.137 ↑ 1.6 105 1

HashAggregate (cost=393.82..394.31 rows=163 width=72) (actual time=4.115..4.137 rows=105 loops=1)

  • Group Key: d.user_role, d.mentee_id, d.mentor_id, d.request_id, d."timestamp", ((SubPlan 4))
10. 0.011 4.065 ↑ 1.6 105 1

Append (cost=0.00..393.33 rows=163 width=72) (actual time=0.922..4.065 rows=105 loops=1)

11. 0.197 3.860 ↑ 1.0 99 1

Seq Scan on request_event_declined d (cost=0.00..244.80 rows=103 width=47) (actual time=0.922..3.860 rows=99 loops=1)

12.          

SubPlan (forSeq Scan)

13. 0.099 3.663 ↓ 0.0 0 99

Limit (cost=2.13..2.13 rows=1 width=16) (actual time=0.037..0.037 rows=0 loops=99)

14. 0.099 3.564 ↓ 0.0 0 99

Sort (cost=2.13..2.13 rows=1 width=16) (actual time=0.036..0.036 rows=0 loops=99)

  • Sort Key: scheduled_2."timestamp" DESC
  • Sort Method: quicksort Memory: 25kB
15. 3.465 3.465 ↓ 0.0 0 99

CTE Scan on scheduled scheduled_2 (cost=0.00..2.13 rows=1 width=16) (actual time=0.034..0.035 rows=0 loops=99)

  • Filter: (("timestamp" < d."timestamp") AND (request_id = d.request_id))
  • Rows Removed by Filter: 304
16. 0.012 0.124 ↑ 7.5 4 1

Seq Scan on request_decline_command dc (cost=0.00..74.02 rows=30 width=72) (actual time=0.034..0.124 rows=4 loops=1)

17.          

SubPlan (forSeq Scan)

18. 0.000 0.112 ↓ 0.0 0 4

Limit (cost=2.13..2.13 rows=1 width=16) (actual time=0.028..0.028 rows=0 loops=4)

19. 0.004 0.112 ↓ 0.0 0 4

Sort (cost=2.13..2.13 rows=1 width=16) (actual time=0.028..0.028 rows=0 loops=4)

  • Sort Key: scheduled_1."timestamp" DESC
  • Sort Method: quicksort Memory: 25kB
20. 0.108 0.108 ↓ 0.0 0 4

CTE Scan on scheduled scheduled_1 (cost=0.00..2.13 rows=1 width=16) (actual time=0.025..0.027 rows=0 loops=4)

  • Filter: (("timestamp" < dc."timestamp") AND (request_id = dc.request_id))
  • Rows Removed by Filter: 304
21. 0.012 0.070 ↑ 15.0 2 1

Seq Scan on request_decline_button db (cost=0.00..74.02 rows=30 width=72) (actual time=0.040..0.070 rows=2 loops=1)

22.          

SubPlan (forSeq Scan)

23. 0.000 0.058 ↓ 0.0 0 2

Limit (cost=2.13..2.13 rows=1 width=16) (actual time=0.029..0.029 rows=0 loops=2)

24. 0.004 0.058 ↓ 0.0 0 2

Sort (cost=2.13..2.13 rows=1 width=16) (actual time=0.029..0.029 rows=0 loops=2)

  • Sort Key: scheduled."timestamp" DESC
  • Sort Method: quicksort Memory: 25kB
25. 0.054 0.054 ↓ 0.0 0 2

CTE Scan on scheduled (cost=0.00..2.13 rows=1 width=16) (actual time=0.027..0.027 rows=0 loops=2)

  • Filter: (("timestamp" < db."timestamp") AND (request_id = db.request_id))
  • Rows Removed by Filter: 304
26. 0.131 5,676.036 ↑ 833.0 1 1

Nested Loop Left Join (cost=513.13..4,460.58 rows=833 width=164) (actual time=5,658.600..5,676.036 rows=1 loops=1)

  • Join Filter: (m.id = tab1.mentor_id)
  • Rows Removed by Join Filter: 212
27. 0.018 124.153 ↑ 833.0 1 1

Nested Loop Left Join (cost=396.47..3,231.31 rows=833 width=100) (actual time=106.718..124.153 rows=1 loops=1)

  • Join Filter: (m.id = c.mentor_id)
  • Rows Removed by Join Filter: 161
28. 0.538 31.125 ↑ 833.0 1 1

Hash Right Join (cost=267.04..3,088.43 rows=833 width=68) (actual time=13.690..31.125 rows=1 loops=1)

  • Hash Cond: (mu.mentor_id = m.id)
  • Filter: (concat(u.first_name, ' ', u.last_name) = 'Ashley Miller'::text)
  • Rows Removed by Filter: 449
29. 23.213 23.213 ↑ 6.7 1,698 1

Foreign Scan (cost=100.00..1,417.65 rows=11,392 width=68) (actual time=3.452..23.213 rows=1,698 loops=1)

  • Relations: (production_data.manager_users mu) LEFT JOIN (production_data.users u)
30. 0.070 7.374 ↑ 6.5 450 1

Hash (cost=156.80..156.80 rows=2,925 width=4) (actual time=7.374..7.374 rows=450 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 48kB
31. 7.304 7.304 ↑ 6.5 450 1

Foreign Scan on mentors m (cost=100.00..156.80 rows=2,925 width=4) (actual time=2.921..7.304 rows=450 loops=1)

32. 0.033 93.010 ↓ 40.5 162 1

Materialize (cost=129.44..129.59 rows=4 width=40) (actual time=90.728..93.010 rows=162 loops=1)

33. 0.017 92.977 ↓ 40.5 162 1

Subquery Scan on c (cost=129.44..129.59 rows=4 width=40) (actual time=90.723..92.977 rows=162 loops=1)

34. 0.449 92.960 ↓ 40.5 162 1

GroupAggregate (cost=129.44..129.58 rows=4 width=40) (actual time=90.722..92.960 rows=162 loops=1)

  • Group Key: b.mentor_id
35. 0.357 92.511 ↓ 425.8 1,703 1

Subquery Scan on b (cost=129.44..129.55 rows=4 width=40) (actual time=90.699..92.511 rows=1,703 loops=1)

  • Filter: (b.row_nb <= 12)
  • Rows Removed by Filter: 2395
36. 1.239 92.154 ↓ 315.2 4,098 1

WindowAgg (cost=129.44..129.50 rows=13 width=56) (actual time=90.698..92.154 rows=4,098 loops=1)

37. 2.739 90.915 ↓ 315.2 4,098 1

Sort (cost=129.44..129.44 rows=13 width=48) (actual time=90.693..90.915 rows=4,098 loops=1)

  • Sort Key: (("*SELECT* 1".mentor_id)::bigint), (("*SELECT* 1"."timestamp")::timestamp with time zone) DESC
  • Sort Method: quicksort Memory: 632kB
38. 3.343 88.176 ↓ 434.5 5,648 1

HashAggregate (cost=129.31..129.35 rows=13 width=48) (actual time=87.190..88.176 rows=5,648 loops=1)

  • Group Key: (("*SELECT* 1".mentor_id)::bigint), (("*SELECT* 1"."timestamp")::timestamp with time zone), ('done'::text)
39. 0.338 84.833 ↓ 434.5 5,649 1

Append (cost=100.00..129.29 rows=13 width=48) (actual time=3.427..84.833 rows=5,649 loops=1)

40. 1.019 80.260 ↓ 507.9 5,587 1

Subquery Scan on *SELECT* 1 (cost=100.00..118.15 rows=11 width=48) (actual time=3.427..80.260 rows=5,587 loops=1)

41. 79.241 79.241 ↓ 507.9 5,587 1

Foreign Scan on requests (cost=100.00..118.11 rows=11 width=44) (actual time=3.426..79.241 rows=5,587 loops=1)

42. 0.030 0.030 ↓ 4.0 4 1

Seq Scan on request_no_show (cost=0.00..10.07 rows=1 width=48) (actual time=0.014..0.030 rows=4 loops=1)

  • Filter: (user_role = 'mentor'::text)
  • Rows Removed by Filter: 3
43. 4.205 4.205 ↓ 58.0 58 1

CTE Scan on correct_request_event_declined (cost=0.00..1.06 rows=1 width=48) (actual time=4.139..4.205 rows=58 loops=1)

  • Filter: (user_role = 'mentor'::text)
  • Rows Removed by Filter: 47
44. 0.266 5,551.752 ↓ 106.0 212 1

Materialize (cost=116.66..1,212.21 rows=2 width=68) (actual time=61.581..5,551.752 rows=212 loops=1)

45. 0.127 5,551.486 ↓ 106.0 212 1

Subquery Scan on tab1 (cost=116.66..1,212.21 rows=2 width=68) (actual time=61.580..5,551.486 rows=212 loops=1)

46. 1.912 5,551.359 ↓ 106.0 212 1

GroupAggregate (cost=116.66..1,212.20 rows=2 width=100) (actual time=61.579..5,551.359 rows=212 loops=1)

  • Group Key: rs.mentor_id
47. 2.690 5,549.447 ↓ 727.5 1,455 1

Subquery Scan on rs (cost=116.66..1,212.19 rows=2 width=12) (actual time=45.172..5,549.447 rows=1,455 loops=1)

  • Filter: ((rs.row_nb <= 8) AND (SubPlan 6))
  • Rows Removed by Filter: 1279
48. 3.764 47.307 ↓ 303.8 2,734 1

WindowAgg (cost=116.66..116.70 rows=9 width=32) (actual time=42.645..47.307 rows=2,734 loops=1)

49. 2.048 43.543 ↓ 303.8 2,734 1

Sort (cost=116.66..116.66 rows=9 width=20) (actual time=42.638..43.543 rows=2,734 loops=1)

  • Sort Key: requests_1.mentor_id, requests_1.call_scheduled_at DESC
  • Sort Method: quicksort Memory: 310kB
50. 41.495 41.495 ↓ 303.8 2,734 1

Foreign Scan on requests requests_1 (cost=100.00..116.63 rows=9 width=20) (actual time=2.891..41.495 rows=2,734 loops=1)

51.          

SubPlan (forSubquery Scan)

52. 1,706.100 5,499.450 ↑ 1.0 1 1,650

GroupAggregate (cost=100.00..121.72 rows=1 width=12) (actual time=3.333..3.333 rows=1 loops=1,650)

  • Group Key: requests_2.mentor_id
  • Filter: (count(requests_2.mentor_id) >= 4)
  • Rows Removed by Filter: 0
53. 3,793.350 3,793.350 ↓ 12.0 12 1,650

Foreign Scan on requests requests_2 (cost=100.00..121.71 rows=1 width=4) (actual time=2.298..2.299 rows=12 loops=1,650)