explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F2JO

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 14,319.815 ↑ 1.0 1 1

Limit (cost=101,223.80..101,224.72 rows=1 width=112) (actual time=14,319.813..14,319.815 rows=1 loops=1)

2.          

CTE scheduled

3. 13.583 6,164.532 ↓ 3.2 16,468 1

HashAggregate (cost=442.60..457.95 rows=5,118 width=24) (actual time=6,160.788..6,164.532 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.155 6,150.949 ↓ 3.2 16,468 1

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

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

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

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

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

7. 252.696 252.696 ↑ 3.4 506 1

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

8.          

CTE correct_request_event_declined

9. 5.819 13,921.760 ↓ 1.5 4,163 1

HashAggregate (cost=100,372.42..100,380.79 rows=2,790 width=72) (actual time=13,920.696..13,921.760 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.698 13,915.941 ↓ 1.5 4,163 1

Append (cost=100.00..100,364.05 rows=2,790 width=72) (actual time=6,242.017..13,915.941 rows=4,163 loops=1)

11. 1,280.169 12,281.289 ↓ 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=6,242.016..12,281.289 rows=3,354 loops=1)

12.          

SubPlan (forForeign Scan)

13. 3.354 11,001.120 ↑ 1.0 1 3,354

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

14. 6.708 10,997.766 ↑ 9.0 1 3,354

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

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

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

  • Filter: (("timestamp" < d."timestamp") AND (request_id = d.ep_request_id))
  • Rows Removed by Filter: 16466
16. 220.946 867.424 ↑ 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=75.278..867.424 rows=427 loops=1)

17.          

SubPlan (forForeign Scan)

18. 0.427 646.478 ↑ 1.0 1 427

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

19. 0.854 646.051 ↑ 9.0 1 427

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

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

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

  • Filter: (("timestamp" < dc."timestamp") AND (request_id = dc.ep_request_id))
  • Rows Removed by Filter: 16467
21. 183.598 766.530 ↑ 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=74.868..766.530 rows=382 loops=1)

22.          

SubPlan (forForeign Scan)

23. 0.382 582.932 ↑ 1.0 1 382

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

24. 0.764 582.550 ↑ 9.0 1 382

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

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

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

  • Filter: (("timestamp" < db."timestamp") AND (request_id = db.ep_request_id))
  • Rows Removed by Filter: 16466
26.          

CTE ranked_actions

27. 2.834 14,313.615 ↓ 457.8 7,783 1

WindowAgg (cost=284.97..285.05 rows=17 width=56) (actual time=14,310.310..14,313.615 rows=7,783 loops=1)

28. 4.072 14,310.781 ↓ 457.8 7,783 1

Sort (cost=284.97..284.98 rows=17 width=48) (actual time=14,310.303..14,310.781 rows=7,783 loops=1)

  • Sort Key: actions.mentor_id, actions.date DESC
  • Sort Method: quicksort Memory: 801kB
29. 0.713 14,306.709 ↓ 457.8 7,783 1

Subquery Scan on actions (cost=284.80..284.90 rows=17 width=48) (actual time=14,304.246..14,306.709 rows=7,783 loops=1)

30. 5.677 14,305.996 ↓ 457.8 7,783 1

HashAggregate (cost=284.80..284.85 rows=17 width=48) (actual time=14,304.244..14,305.996 rows=7,783 loops=1)

  • Group Key: (("*SELECT* 1".mentor_id)::bigint), ('Call done'::text), (("*SELECT* 1".date)::timestamp with time zone)
31. 0.588 14,300.319 ↓ 457.9 7,784 1

Append (cost=100.00..284.77 rows=17 width=48) (actual time=3.087..14,300.319 rows=7,784 loops=1)

32. 1.301 80.645 ↓ 653.5 7,188 1

Subquery Scan on *SELECT* 1 (cost=100.00..118.15 rows=11 width=48) (actual time=3.087..80.645 rows=7,188 loops=1)

33. 79.344 79.344 ↓ 653.5 7,188 1

Foreign Scan on requests (cost=100.00..118.11 rows=11 width=44) (actual time=3.074..79.344 rows=7,188 loops=1)

34. 148.495 148.495 ↓ 297.0 297 1

Foreign Scan (cost=100.00..125.65 rows=1 width=48) (actual time=76.330..148.495 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.958 14,070.591 ↓ 59.8 299 1

Nested Loop Anti Join (cost=0.00..40.95 rows=5 width=48) (actual time=13,923.447..14,070.591 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,921.341 13,921.341 ↓ 74.4 372 1

CTE Scan on correct_request_event_declined cred (cost=0.00..22.32 rows=5 width=32) (actual time=13,920.724..13,921.341 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. 134.292 134.292 ↓ 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.361 rows=346 loops=372)

  • Filter: (originator_role = 'mentor'::text)
  • Rows Removed by Filter: 3418
38. 0.012 14,319.811 ↑ 58.0 1 1

GroupAggregate (cost=100.00..153.64 rows=58 width=112) (actual time=14,319.811..14,319.811 rows=1 loops=1)

  • Group Key: ra.mentor_id, u.first_name, u.last_name
39. 0.004 14,319.799 ↑ 7.2 8 1

Nested Loop (cost=100.00..153.06 rows=58 width=104) (actual time=14,317.409..14,319.799 rows=8 loops=1)

40. 3.852 3.852 ↑ 58.0 1 1

Foreign Scan (cost=100.00..152.59 rows=58 width=68) (actual time=3.850..3.852 rows=1 loops=1)

  • Relations: (plato_data.manager_users mu) INNER JOIN (plato_data.users u)
41. 14,315.943 14,315.943 ↓ 8.0 8 1

CTE Scan on ranked_actions ra (cost=0.00..0.12 rows=1 width=40) (actual time=14,313.557..14,315.943 rows=8 loops=1)

  • Filter: ((rank <= 8) AND (mentor_id = 289))
  • Rows Removed by Filter: 7775
Planning time : 2.582 ms
Execution time : 15,034.624 ms