explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DfHn5

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 124.379 ↑ 100.0 1 1

Limit (cost=875.43..1,216.90 rows=100 width=100) (actual time=106.312..124.379 rows=1 loops=1)

2.          

CTE scheduled

3. 0.148 0.768 ↑ 1.0 304 1

HashAggregate (cost=83.74..84.65 rows=304 width=24) (actual time=0.726..0.768 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.019 0.620 ↑ 1.0 304 1

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

5. 0.470 0.470 ↑ 1.0 254 1

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

6. 0.077 0.077 ↓ 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.077 rows=29 loops=1)

7. 0.054 0.054 ↑ 1.4 21 1

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

8.          

CTE correct_request_event_declined

9. 0.077 4.114 ↑ 1.6 105 1

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

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

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

11. 0.273 3.837 ↑ 1.0 99 1

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

12.          

SubPlan (forSeq Scan)

13. 0.000 3.564 ↓ 0.0 0 99

Limit (cost=2.13..2.13 rows=1 width=16) (actual time=0.036..0.036 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.033..0.035 rows=0 loops=99)

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

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

17.          

SubPlan (forSeq Scan)

18. 0.004 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.108 ↓ 0.0 0 4

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

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

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

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

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

22.          

SubPlan (forSeq Scan)

23. 0.002 0.060 ↓ 0.0 0 2

Limit (cost=2.13..2.13 rows=1 width=16) (actual time=0.030..0.030 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.032 124.378 ↑ 833.0 1 1

Nested Loop Left Join (cost=396.47..3,240.89 rows=833 width=100) (actual time=106.311..124.378 rows=1 loops=1)

  • Join Filter: (m.id = c.mentor_id)
  • Rows Removed by Join Filter: 161
27. 0.554 32.186 ↑ 833.0 1 1

Hash Right Join (cost=267.04..3,088.43 rows=833 width=68) (actual time=14.120..32.186 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
28. 23.785 23.785 ↑ 6.7 1,698 1

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

  • Relations: (production_data.manager_users mu) LEFT JOIN (production_data.users u)
29. 0.072 7.847 ↑ 6.5 450 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 48kB
30. 7.775 7.775 ↑ 6.5 450 1

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

31. 0.030 92.160 ↓ 40.5 162 1

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

32. 0.016 92.130 ↓ 40.5 162 1

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

33. 0.431 92.114 ↓ 40.5 162 1

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

  • Group Key: b.mentor_id
34. 0.287 91.683 ↓ 425.8 1,703 1

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

  • Filter: (b.row_nb <= 12)
  • Rows Removed by Filter: 2395
35. 1.250 91.396 ↓ 315.2 4,098 1

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

36. 2.686 90.146 ↓ 315.2 4,098 1

Sort (cost=129.44..129.44 rows=13 width=48) (actual time=89.919..90.146 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
37. 3.436 87.460 ↓ 434.5 5,648 1

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

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

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

39. 0.982 79.472 ↓ 507.9 5,587 1

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

40. 78.490 78.490 ↓ 507.9 5,587 1

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

41. 0.035 0.035 ↓ 4.0 4 1

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

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

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

  • Filter: (user_role = 'mentor'::text)
  • Rows Removed by Filter: 47