explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sHu1

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 14,477.994 ↑ 1.0 1 1

Limit (cost=101,223.54..101,307.97 rows=1 width=112) (actual time=14,477.988..14,477.994 rows=1 loops=1)

2.          

CTE scheduled

3. 14.337 6,018.563 ↓ 3.2 16,468 1

HashAggregate (cost=442.60..457.95 rows=5,118 width=24) (actual time=6,014.869..6,018.563 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.241 6,004.226 ↓ 3.2 16,468 1

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

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

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

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

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

7. 245.654 245.654 ↑ 3.4 506 1

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

8.          

CTE correct_request_event_declined

9. 6.168 13,661.428 ↓ 1.5 4,163 1

HashAggregate (cost=100,372.42..100,380.79 rows=2,790 width=72) (actual time=13,660.343..13,661.428 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.898 13,655.260 ↓ 1.5 4,163 1

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

11. 1,246.576 12,029.686 ↓ 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,094.663..12,029.686 rows=3,354 loops=1)

12.          

SubPlan (for Foreign Scan)

13. 3.354 10,783.110 ↑ 1.0 1 3,354

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

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

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

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

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

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

17.          

SubPlan (for Foreign Scan)

18. 0.427 664.412 ↑ 1.0 1 427

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

19. 0.854 663.985 ↑ 9.0 1 427

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

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

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

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

22.          

SubPlan (for Foreign Scan)

23. 0.000 568.034 ↑ 1.0 1 382

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

24. 1.146 568.034 ↑ 9.0 1 382

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

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

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

  • Filter: (("timestamp" < db."timestamp") AND (request_id = db.ep_request_id))
  • Rows Removed by Filter: 16466
26. 0.020 14,477.987 ↑ 58.0 1 1

GroupAggregate (cost=384.80..5,281.79 rows=58 width=112) (actual time=14,477.987..14,477.987 rows=1 loops=1)

  • Group Key: tbl.mentor_id, u.first_name, u.last_name
27. 0.007 14,477.967 ↑ 7.2 8 1

Nested Loop (cost=384.80..5,281.21 rows=58 width=104) (actual time=14,419.987..14,477.967 rows=8 loops=1)

28. 3.755 3.755 ↑ 58.0 1 1

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

  • Relations: (plato_data.manager_users mu) INNER JOIN (plato_data.users u)
29. 0.019 14,474.205 ↓ 8.0 8 1

Materialize (cost=284.80..5,128.41 rows=1 width=40) (actual time=14,416.232..14,474.205 rows=8 loops=1)

30. 0.506 14,474.186 ↓ 8.0 8 1

Subquery Scan on tbl (cost=284.80..5,128.41 rows=1 width=40) (actual time=14,416.218..14,474.186 rows=8 loops=1)

  • Filter: ((tbl.mentor_id = 289) AND (SubPlan 6))
  • Rows Removed by Filter: 7775
31. 5.721 14,042.436 ↓ 457.8 7,783 1

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

  • Group Key: (("*SELECT* 1".mentor_id)::bigint), ('Call done'::text), (("*SELECT* 1".action_date)::timestamp with time zone)
32. 0.540 14,036.715 ↓ 457.9 7,784 1

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

33. 1.279 81.785 ↓ 653.5 7,188 1

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

34. 80.506 80.506 ↓ 653.5 7,188 1

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

35. 145.222 145.222 ↓ 297.0 297 1

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

  • Relations: (plato_api.request_no_show_declared_mentor ns1) LEFT JOIN (plato_api.request_no_show_declared_mentor ns2)
36. 14.256 13,809.168 ↓ 59.8 299 1

Nested Loop Anti Join (cost=0.00..40.95 rows=5 width=48) (actual time=13,663.147..13,809.168 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
37. 13,660.992 13,660.992 ↓ 74.4 372 1

CTE Scan on correct_request_event_declined cred (cost=0.00..22.32 rows=5 width=32) (actual time=13,660.381..13,660.992 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
38. 133.920 133.920 ↓ 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.360 rows=346 loops=372)

  • Filter: (originator_role = 'mentor'::text)
  • Rows Removed by Filter: 3418
39.          

SubPlan (for Subquery Scan)

40. 0.072 431.244 ↓ 7.0 7 36

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

41. 0.288 431.172 ↓ 7.0 7 36

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

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

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

  • Filter: (tbl.mentor_id = tbl2.mentor_id)
  • Rows Removed by Filter: 7747
43. 45.216 414.576 ↓ 457.8 7,783 36

HashAggregate (cost=284.80..284.85 rows=17 width=48) (actual time=10.372..11.516 rows=7,783 loops=36)

  • Group Key: (("*SELECT* 1_1".mentor_id)::bigint), ('Call done'::text), (("*SELECT* 1_1".action_date)::timestamp with time zone)
44. 0.532 369.360 ↓ 457.9 7,784 1

Append (cost=100.00..284.77 rows=17 width=48) (actual time=2.484..369.360 rows=7,784 loops=1)

45. 1.268 80.449 ↓ 653.5 7,188 1

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

46. 79.181 79.181 ↓ 653.5 7,188 1

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

47. 142.362 142.362 ↓ 297.0 297 1

Foreign Scan (cost=100.00..125.65 rows=1 width=48) (actual time=72.156..142.362 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.457 146.017 ↓ 59.8 299 1

Nested Loop Anti Join (cost=0.00..40.95 rows=5 width=48) (actual time=0.503..146.017 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.616 0.616 ↓ 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.616 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. 130.944 130.944 ↓ 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.352 rows=346 loops=372)

  • Filter: (originator_role = 'mentor'::text)
  • Rows Removed by Filter: 3418
Planning time : 3.302 ms
Execution time : 15,196.495 ms