explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xIRc

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 23,590.629 ↑ 200.0 1 1

GroupAggregate (cost=107,832.20..107,836.28 rows=200 width=112) (actual time=23,590.629..23,590.629 rows=1 loops=1)

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

CTE scheduled

3. 13.714 6,008.274 ↓ 3.2 16,468 1

HashAggregate (cost=442.60..457.95 rows=5,118 width=24) (actual time=6,004.603..6,008.274 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.205 5,994.560 ↓ 3.2 16,468 1

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

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

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

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

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

7. 245.237 245.237 ↑ 3.4 506 1

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

8.          

CTE correct_request_event_declined

9. 5.955 13,656.525 ↓ 1.5 4,163 1

HashAggregate (cost=100,372.42..100,380.79 rows=2,790 width=72) (actual time=13,655.413..13,656.525 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.730 13,650.570 ↓ 1.5 4,163 1

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

11. 1,237.641 12,050.937 ↓ 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,084.069..12,050.937 rows=3,354 loops=1)

12.          

SubPlan (forForeign Scan)

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

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

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

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

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

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

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

17.          

SubPlan (forForeign Scan)

18. 0.427 637.938 ↑ 1.0 1 427

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

19. 0.854 637.511 ↑ 9.0 1 427

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

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

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

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

22.          

SubPlan (forForeign Scan)

23. 0.382 572.618 ↑ 1.0 1 382

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

24. 1.146 572.236 ↑ 9.0 1 382

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

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

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

  • Filter: (("timestamp" < db."timestamp") AND (request_id = db.ep_request_id))
  • Rows Removed by Filter: 16466
26. 0.026 23,590.622 ↑ 463.0 1 1

Sort (cost=6,993.46..6,993.69 rows=463 width=104) (actual time=23,590.622..23,590.622 rows=1 loops=1)

  • Sort Key: u.first_name, u.last_name
  • Sort Method: quicksort Memory: 25kB
27. 0.015 23,590.596 ↑ 463.0 1 1

Nested Loop Left Join (cost=494.10..6,989.36 rows=463 width=104) (actual time=15,779.996..23,590.596 rows=1 loops=1)

  • Join Filter: (me.id = mu.mentor_id)
28. 0.263 23,587.437 ↑ 8.0 1 1

Nested Loop (cost=394.10..6,849.28 rows=8 width=44) (actual time=15,776.850..23,587.437 rows=1 loops=1)

  • Join Filter: (SubPlan 6)
  • Rows Removed by Join Filter: 35
29. 0.444 13,894.210 ↓ 36.0 36 1

Subquery Scan on tbl (cost=294.10..294.21 rows=1 width=48) (actual time=13,891.420..13,894.210 rows=36 loops=1)

  • Filter: (tbl.mentor_id = 289)
  • Rows Removed by Filter: 7726
30. 6.228 13,893.766 ↓ 456.6 7,762 1

HashAggregate (cost=294.10..294.15 rows=17 width=48) (actual time=13,891.404..13,893.766 rows=7,762 loops=1)

  • Group Key: (("*SELECT* 1".mentor_id)::bigint), ('Call done'::text), (("*SELECT* 1".action_date)::timestamp with time zone)
31. 0.581 13,887.538 ↓ 456.6 7,763 1

Append (cost=100.00..294.08 rows=17 width=48) (actual time=3.621..13,887.538 rows=7,763 loops=1)

32. 1.358 82.333 ↓ 653.4 7,187 1

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

33. 80.975 80.975 ↓ 653.4 7,187 1

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

34. 144.728 144.728 ↓ 297.0 297 1

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

  • Relations: (plato_api.request_no_show_declared_mentor ns1) LEFT JOIN (plato_api.request_no_show_declared_mentor ns2)
35. 0.250 13,659.896 ↓ 55.8 279 1

Hash Anti Join (cost=27.90..50.26 rows=5 width=48) (actual time=13,659.128..13,659.896 rows=279 loops=1)

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

CTE Scan on correct_request_event_declined cred (cost=0.00..22.32 rows=5 width=32) (actual time=13,655.440..13,656.003 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. 0.729 3.643 ↓ 1.3 3,585 1

Hash (cost=16.74..16.74 rows=2,790 width=24) (actual time=3.643..3.643 rows=3,585 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
38. 2.914 2.914 ↓ 1.5 4,163 1

CTE Scan on correct_request_event_declined cred2 (cost=0.00..16.74 rows=2,790 width=24) (actual time=0.001..2.914 rows=4,163 loops=1)

39. 2.484 2.484 ↑ 15.0 1 36

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

40.          

SubPlan (forNested Loop)

41. 0.360 9,690.480 ↓ 8.0 8 36

Limit (cost=428.97..428.97 rows=1 width=8) (actual time=269.173..269.180 rows=8 loops=36)

42. 1.944 9,690.120 ↓ 8.0 8 36

Sort (cost=428.97..428.97 rows=1 width=8) (actual time=269.170..269.170 rows=8 loops=36)

  • Sort Key: tbl2.action_date DESC
  • Sort Method: top-N heapsort Memory: 25kB
43. 20.196 9,688.176 ↓ 333.0 333 36

Subquery Scan on tbl2 (cost=428.81..428.97 rows=1 width=8) (actual time=266.951..269.116 rows=333 loops=36)

  • Filter: (me.id = tbl2.mentor_id)
  • Rows Removed by Filter: 7429
44. 217.368 9,667.980 ↓ 323.4 7,762 36

HashAggregate (cost=428.81..428.89 rows=24 width=48) (actual time=266.861..268.555 rows=7,762 loops=36)

  • Group Key: (("*SELECT* 1_1".mentor_id)::bigint), ('Call done'::text), (("*SELECT* 1_1".action_date)::timestamp with time zone)
45. 1,228.032 9,450.612 ↓ 323.5 7,763 36

Append (cost=100.00..428.78 rows=24 width=48) (actual time=1.198..262.517 rows=7,763 loops=36)

46. 48.492 2,803.896 ↓ 653.4 7,187 36

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

47. 2,755.404 2,755.404 ↓ 653.4 7,187 36

Foreign Scan on requests requests_1 (cost=100.00..118.11 rows=11 width=44) (actual time=1.196..76.539 rows=7,187 loops=36)

48. 7.649 5,384.016 ↓ 37.1 297 36

Hash Anti Join (cost=244.12..260.33 rows=8 width=48) (actual time=44.223..149.556 rows=297 loops=36)

  • Hash Cond: ((ns1_1.ep_request_id = ns2_1.ep_request_id) AND (ns1_1.ep_request_call_scheduled_date = ns2_1.ep_request_call_scheduled_date))
  • Join Filter: (ns2_1."timestamp" > ns1_1."timestamp")
  • Rows Removed by Join Filter: 281
49. 5,095.620 5,095.620 ↓ 35.0 315 36

Foreign Scan on request_no_show_declared_mentor ns1_1 (cost=100.00..116.09 rows=9 width=24) (actual time=36.416..141.545 rows=315 loops=36)

50. 0.139 280.747 ↑ 3.0 560 1

Hash (cost=137.30..137.30 rows=1,706 width=24) (actual time=280.745..280.747 rows=560 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 47kB
51. 280.608 280.608 ↑ 2.6 644 1

Foreign Scan on request_no_show_declared_mentor ns2_1 (cost=100.00..137.30 rows=1,706 width=24) (actual time=69.720..280.608 rows=644 loops=1)

52. 9.406 34.668 ↓ 55.8 279 36

Hash Anti Join (cost=27.90..50.26 rows=5 width=48) (actual time=0.053..0.963 rows=279 loops=36)

  • Hash Cond: ((cred_1.request_id = cred2_1.request_id) AND (cred_1.request_call_scheduled_date = cred2_1.request_call_scheduled_date))
  • Join Filter: (cred2_1."timestamp" < cred_1."timestamp")
  • Rows Removed by Join Filter: 460
53. 23.940 23.940 ↓ 74.4 372 36

CTE Scan on correct_request_event_declined cred_1 (cost=0.00..22.32 rows=5 width=32) (actual time=0.011..0.665 rows=372 loops=36)

  • Filter: ((originator_role = 'mentor'::text) AND (date_part('day'::text, (request_call_scheduled_date - "timestamp")) >= '0'::double precision))
  • Rows Removed by Filter: 3791
54. 0.639 1.322 ↓ 1.3 3,585 1

Hash (cost=16.74..16.74 rows=2,790 width=24) (actual time=1.321..1.322 rows=3,585 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
55. 0.683 0.683 ↓ 1.5 4,163 1

CTE Scan on correct_request_event_declined cred2_1 (cost=0.00..16.74 rows=2,790 width=24) (actual time=0.001..0.683 rows=4,163 loops=1)

56. 0.002 3.144 ↑ 58.0 1 1

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

57. 3.142 3.142 ↑ 58.0 1 1

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

  • Relations: (plato_data.manager_users mu) LEFT JOIN (plato_data.users u)
Planning time : 3.396 ms
Execution time : 24,348.683 ms