explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KY3t

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 14,266.359 ↑ 200.0 1 1

GroupAggregate (cost=105,661.99..105,666.06 rows=200 width=112) (actual time=14,266.359..14,266.359 rows=1 loops=1)

  • Group Key: tbl.mentor_id, u.first_name, u.last_name
2.          

CTE scheduled

3. 14.030 5,878.197 ↓ 3.2 16,468 1

HashAggregate (cost=442.60..457.95 rows=5,118 width=24) (actual time=5,874.437..5,878.197 rows=16,468 loops=1)

  • Group Key: request_scheduled_mentor.ep_request_id, request_scheduled_mentor."timestamp", (to_timestamp(((request_scheduled_mentor.ep_request_call_scheduled_date / 1000))::double precision))
4. 1.251 5,864.167 ↓ 3.2 16,468 1

Append (cost=100.00..434.92 rows=5,118 width=24) (actual time=69.818..5,864.167 rows=16,468 loops=1)

5. 4,415.516 4,415.516 ↓ 7.4 12,608 1

Foreign Scan on request_scheduled_mentor (cost=100.00..139.85 rows=1,706 width=24) (actual time=69.817..4,415.516 rows=12,608 loops=1)

6. 1,206.031 1,206.031 ↓ 2.0 3,354 1

Foreign Scan on request_event_updated_mentor (cost=100.00..139.85 rows=1,706 width=24) (actual time=68.692..1,206.031 rows=3,354 loops=1)

7. 241.369 241.369 ↑ 3.4 506 1

Foreign Scan on request_auto_rescheduled_mentor (cost=100.00..139.85 rows=1,706 width=24) (actual time=69.187..241.369 rows=506 loops=1)

8.          

CTE correct_request_event_declined

9. 6.177 13,459.138 ↓ 1.5 4,163 1

HashAggregate (cost=100,372.42..100,380.79 rows=2,790 width=72) (actual time=13,458.014..13,459.138 rows=4,163 loops=1)

  • Group Key: d.originator_role, d.ep_mentee_id, d.ep_mentor_id, d.ep_request_id, d."timestamp", ((SubPlan 4))
10. 0.646 13,452.961 ↓ 1.5 4,163 1

Append (cost=100.00..100,364.05 rows=2,790 width=72) (actual time=5,952.456..13,452.961 rows=4,163 loops=1)

11. 1,218.885 11,871.189 ↓ 3.6 3,354 1

Foreign Scan on request_event_updated_mentor d (cost=100.00..33,451.89 rows=930 width=72) (actual time=5,952.455..11,871.189 rows=3,354 loops=1)

12.          

SubPlan (forForeign Scan)

13. 0.000 10,652.304 ↑ 1.0 1 3,354

Limit (cost=35.84..35.84 rows=1 width=16) (actual time=3.176..3.176 rows=1 loops=3,354)

14. 10.062 10,652.304 ↑ 9.0 1 3,354

Sort (cost=35.84..35.84 rows=9 width=16) (actual time=3.175..3.176 rows=1 loops=3,354)

  • Sort Key: scheduled_2."timestamp" DESC
  • Sort Method: top-N heapsort Memory: 25kB
15. 10,642.242 10,642.242 ↑ 4.5 2 3,354

CTE Scan on scheduled scheduled_2 (cost=0.00..35.83 rows=9 width=16) (actual time=2.463..3.173 rows=2 loops=3,354)

  • Filter: (("timestamp" < d."timestamp") AND (request_id = d.ep_request_id))
  • Rows Removed by Filter: 16466
16. 208.650 837.621 ↑ 2.2 427 1

Foreign Scan on request_decline_command_executed_mentor dc (cost=100.00..33,451.89 rows=930 width=72) (actual time=71.039..837.621 rows=427 loops=1)

17.          

SubPlan (forForeign Scan)

18. 0.427 628.971 ↑ 1.0 1 427

Limit (cost=35.84..35.84 rows=1 width=16) (actual time=1.473..1.473 rows=1 loops=427)

19. 0.854 628.544 ↑ 9.0 1 427

Sort (cost=35.84..35.84 rows=9 width=16) (actual time=1.472..1.472 rows=1 loops=427)

  • Sort Key: scheduled_1."timestamp" DESC
  • Sort Method: quicksort Memory: 25kB
20. 627.690 627.690 ↑ 9.0 1 427

CTE Scan on scheduled scheduled_1 (cost=0.00..35.83 rows=9 width=16) (actual time=0.679..1.470 rows=1 loops=427)

  • Filter: (("timestamp" < dc."timestamp") AND (request_id = dc.ep_request_id))
  • Rows Removed by Filter: 16467
21. 174.707 743.505 ↑ 2.4 382 1

Foreign Scan on request_decline_button_clicked_mentor db (cost=100.00..33,451.89 rows=930 width=72) (actual time=71.300..743.505 rows=382 loops=1)

22.          

SubPlan (forForeign Scan)

23. 0.000 568.798 ↑ 1.0 1 382

Limit (cost=35.84..35.84 rows=1 width=16) (actual time=1.489..1.489 rows=1 loops=382)

24. 0.764 568.798 ↑ 9.0 1 382

Sort (cost=35.84..35.84 rows=9 width=16) (actual time=1.489..1.489 rows=1 loops=382)

  • Sort Key: scheduled."timestamp" DESC
  • Sort Method: quicksort Memory: 25kB
25. 568.034 568.034 ↑ 4.5 2 382

CTE Scan on scheduled (cost=0.00..35.83 rows=9 width=16) (actual time=0.608..1.487 rows=2 loops=382)

  • Filter: (("timestamp" < db."timestamp") AND (request_id = db.ep_request_id))
  • Rows Removed by Filter: 16466
26. 0.036 14,266.345 ↑ 57.9 8 1

Sort (cost=4,823.25..4,823.48 rows=463 width=104) (actual time=14,266.345..14,266.345 rows=8 loops=1)

  • Sort Key: u.first_name, u.last_name
  • Sort Method: quicksort Memory: 25kB
27. 0.009 14,266.309 ↑ 57.9 8 1

Nested Loop Left Join (cost=484.80..4,819.15 rows=463 width=104) (actual time=14,207.825..14,266.309 rows=8 loops=1)

  • Join Filter: (me.id = mu.mentor_id)
28. 0.094 14,263.028 ↑ 1.0 8 1

Nested Loop (cost=384.80..4,679.06 rows=8 width=44) (actual time=14,204.552..14,263.028 rows=8 loops=1)

  • Join Filter: (SubPlan 6)
  • Rows Removed by Join Filter: 28
29. 0.426 13,836.262 ↓ 36.0 36 1

Subquery Scan on tbl (cost=284.80..284.91 rows=1 width=48) (actual time=13,834.077..13,836.262 rows=36 loops=1)

  • Filter: (tbl.mentor_id = 289)
  • Rows Removed by Filter: 7746
30. 5.746 13,835.836 ↓ 457.8 7,782 1

HashAggregate (cost=284.80..284.85 rows=17 width=48) (actual time=13,834.059..13,835.836 rows=7,782 loops=1)

  • Group Key: (("*SELECT* 1".mentor_id)::bigint), ('Call done'::text), (("*SELECT* 1".action_date)::timestamp with time zone)
31. 0.549 13,830.090 ↓ 457.8 7,783 1

Append (cost=100.00..284.77 rows=17 width=48) (actual time=3.718..13,830.090 rows=7,783 loops=1)

32. 1.306 82.490 ↓ 653.4 7,187 1

Subquery Scan on *SELECT* 1 (cost=100.00..118.15 rows=11 width=48) (actual time=3.717..82.490 rows=7,187 loops=1)

33. 81.184 81.184 ↓ 653.4 7,187 1

Foreign Scan on requests (cost=100.00..118.11 rows=11 width=44) (actual time=3.709..81.184 rows=7,187 loops=1)

34. 141.890 141.890 ↓ 297.0 297 1

Foreign Scan (cost=100.00..125.65 rows=1 width=48) (actual time=73.076..141.890 rows=297 loops=1)

  • Relations: (plato_api.request_no_show_declared_mentor ns1) LEFT JOIN (plato_api.request_no_show_declared_mentor ns2)
35. 14.453 13,605.161 ↓ 59.8 299 1

Nested Loop Anti Join (cost=0.00..40.95 rows=5 width=48) (actual time=13,460.861..13,605.161 rows=299 loops=1)

  • Join Filter: ((cred2."timestamp" < cred."timestamp") AND (cred.request_id = cred2.request_id) AND (cred.request_call_scheduled_date = cred2.request_call_scheduled_date))
  • Rows Removed by Join Filter: 128557
36. 13,458.648 13,458.648 ↓ 74.4 372 1

CTE Scan on correct_request_event_declined cred (cost=0.00..22.32 rows=5 width=32) (actual time=13,458.051..13,458.648 rows=372 loops=1)

  • Filter: ((originator_role = 'mentor'::text) AND (date_part('day'::text, (request_call_scheduled_date - "timestamp")) >= '0'::double precision))
  • Rows Removed by Filter: 3791
37. 132.060 132.060 ↓ 24.7 346 372

CTE Scan on correct_request_event_declined cred2 (cost=0.00..18.14 rows=14 width=24) (actual time=0.002..0.355 rows=346 loops=372)

  • Filter: (originator_role = 'mentor'::text)
  • Rows Removed by Filter: 3418
38. 2.556 2.556 ↑ 15.0 1 36

Foreign Scan on mentors me (cost=100.00..120.43 rows=15 width=4) (actual time=0.071..0.071 rows=1 loops=36)

39.          

SubPlan (forNested Loop)

40. 0.072 424.116 ↓ 7.0 7 36

Limit (cost=284.91..284.91 rows=1 width=8) (actual time=11.779..11.781 rows=7 loops=36)

41. 0.288 424.044 ↓ 7.0 7 36

Sort (cost=284.91..284.91 rows=1 width=8) (actual time=11.779..11.779 rows=7 loops=36)

  • Sort Key: tbl2.action_date DESC
  • Sort Method: top-N heapsort Memory: 25kB
42. 16.380 423.756 ↓ 36.0 36 36

Subquery Scan on tbl2 (cost=284.80..284.91 rows=1 width=8) (actual time=10.168..11.771 rows=36 loops=36)

  • Filter: (me.id = tbl2.mentor_id)
  • Rows Removed by Filter: 7746
43. 45.402 407.376 ↓ 457.8 7,782 36

HashAggregate (cost=284.80..284.85 rows=17 width=48) (actual time=10.160..11.316 rows=7,782 loops=36)

  • Group Key: (("*SELECT* 1_1".mentor_id)::bigint), ('Call done'::text), (("*SELECT* 1_1".action_date)::timestamp with time zone)
44. 0.533 361.974 ↓ 457.8 7,783 1

Append (cost=100.00..284.77 rows=17 width=48) (actual time=1.980..361.974 rows=7,783 loops=1)

45. 1.262 75.138 ↓ 653.4 7,187 1

Subquery Scan on *SELECT* 1_1 (cost=100.00..118.15 rows=11 width=48) (actual time=1.979..75.138 rows=7,187 loops=1)

46. 73.876 73.876 ↓ 653.4 7,187 1

Foreign Scan on requests requests_1 (cost=100.00..118.11 rows=11 width=44) (actual time=1.977..73.876 rows=7,187 loops=1)

47. 139.931 139.931 ↓ 297.0 297 1

Foreign Scan (cost=100.00..125.65 rows=1 width=48) (actual time=70.954..139.931 rows=297 loops=1)

  • Relations: (plato_api.request_no_show_declared_mentor ns1) LEFT JOIN (plato_api.request_no_show_declared_mentor ns2)
48. 14.442 146.372 ↓ 59.8 299 1

Nested Loop Anti Join (cost=0.00..40.95 rows=5 width=48) (actual time=0.483..146.372 rows=299 loops=1)

  • Join Filter: ((cred2_1."timestamp" < cred_1."timestamp") AND (cred_1.request_id = cred2_1.request_id) AND (cred_1.request_call_scheduled_date = cred2_1.request_call_scheduled_date))
  • Rows Removed by Join Filter: 128557
49. 0.614 0.614 ↓ 74.4 372 1

CTE Scan on correct_request_event_declined cred_1 (cost=0.00..22.32 rows=5 width=32) (actual time=0.012..0.614 rows=372 loops=1)

  • Filter: ((originator_role = 'mentor'::text) AND (date_part('day'::text, (request_call_scheduled_date - "timestamp")) >= '0'::double precision))
  • Rows Removed by Filter: 3791
50. 131.316 131.316 ↓ 24.7 346 372

CTE Scan on correct_request_event_declined cred2_1 (cost=0.00..18.14 rows=14 width=24) (actual time=0.002..0.353 rows=346 loops=372)

  • Filter: (originator_role = 'mentor'::text)
  • Rows Removed by Filter: 3418
51. 0.008 3.272 ↑ 58.0 1 8

Materialize (cost=100.00..138.26 rows=58 width=68) (actual time=0.409..0.409 rows=1 loops=8)

52. 3.264 3.264 ↑ 58.0 1 1

Foreign Scan (cost=100.00..138.20 rows=58 width=68) (actual time=3.264..3.264 rows=1 loops=1)

  • Relations: (plato_data.manager_users mu) LEFT JOIN (plato_data.users u)
Planning time : 3.577 ms
Execution time : 14,975.875 ms