explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VrSB

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 544.767 ↑ 67.0 1 1

GroupAggregate (cost=1,086.89..1,087.60 rows=67 width=108) (actual time=544.766..544.767 rows=1 loops=1)

  • Group Key: 289, u.first_name, u.last_name
2.          

CTE scheduled

3. 0.074 237.168 ↓ 3.5 95 1

HashAggregate (cost=348.43..348.51 rows=27 width=24) (actual time=237.148..237.168 rows=95 loops=1)

  • Group Key: rs.ep_request_id, rs."timestamp", (to_timestamp(((rs.ep_request_call_scheduled_date / 1000))::double precision))
4. 0.010 237.094 ↓ 3.5 95 1

Append (cost=100.00..348.39 rows=27 width=24) (actual time=88.434..237.094 rows=95 loops=1)

5. 88.459 88.459 ↓ 9.1 82 1

Foreign Scan on request_scheduled_mentor rs (cost=100.00..116.10 rows=9 width=24) (actual time=88.433..88.459 rows=82 loops=1)

6. 75.670 75.670 ↓ 1.4 13 1

Foreign Scan on request_event_updated_mentor ru (cost=100.00..116.10 rows=9 width=24) (actual time=75.666..75.670 rows=13 loops=1)

7. 72.955 72.955 ↓ 0.0 0 1

Foreign Scan on request_auto_rescheduled_mentor rr (cost=100.00..116.10 rows=9 width=24) (actual time=72.955..72.955 rows=0 loops=1)

8.          

CTE correct_request_event_declined

9. 0.004 460.766 ↑ 1.5 2 1

Unique (cost=342.92..342.93 rows=3 width=64) (actual time=460.764..460.766 rows=2 loops=1)

10. 0.081 460.762 ↑ 1.5 2 1

Sort (cost=342.92..342.92 rows=3 width=64) (actual time=460.762..460.762 rows=2 loops=1)

  • Sort Key: d.originator_role, d.ep_mentee_id, d.ep_request_id, d."timestamp", ((SubPlan 4))
  • Sort Method: quicksort Memory: 25kB
11. 0.004 460.681 ↑ 1.5 2 1

Append (cost=100.00..342.91 rows=3 width=64) (actual time=387.901..460.681 rows=2 loops=1)

12. 76.779 76.779 ↓ 0.0 0 1

Foreign Scan on request_event_updated_mentor d (cost=100.00..114.30 rows=1 width=64) (actual time=76.779..76.779 rows=0 loops=1)

13.          

SubPlan (forForeign Scan)

14. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.19..0.19 rows=1 width=16) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Sort (cost=0.19..0.19 rows=1 width=16) (never executed)

  • Sort Key: scheduled_2."timestamp" DESC
16. 0.000 0.000 ↓ 0.0 0

CTE Scan on scheduled scheduled_2 (cost=0.00..0.19 rows=1 width=16) (never executed)

  • Filter: (("timestamp" < d."timestamp") AND (request_id = d.ep_request_id))
17. 73.887 311.155 ↓ 2.0 2 1

Foreign Scan on request_decline_command_executed_mentor dc (cost=100.00..114.30 rows=1 width=64) (actual time=311.119..311.155 rows=2 loops=1)

18.          

SubPlan (forForeign Scan)

19. 0.006 237.268 ↑ 1.0 1 2

Limit (cost=0.19..0.19 rows=1 width=16) (actual time=118.632..118.634 rows=1 loops=2)

20. 0.040 237.262 ↑ 1.0 1 2

Sort (cost=0.19..0.19 rows=1 width=16) (actual time=118.631..118.631 rows=1 loops=2)

  • Sort Key: scheduled_1."timestamp" DESC
  • Sort Method: quicksort Memory: 25kB
21. 237.222 237.222 ↑ 1.0 1 2

CTE Scan on scheduled scheduled_1 (cost=0.00..0.19 rows=1 width=16) (actual time=118.597..118.611 rows=1 loops=2)

  • Filter: (("timestamp" < dc."timestamp") AND (request_id = dc.ep_request_id))
  • Rows Removed by Filter: 94
22. 72.743 72.743 ↓ 0.0 0 1

Foreign Scan on request_decline_button_clicked_mentor db (cost=100.00..114.30 rows=1 width=64) (actual time=72.743..72.743 rows=0 loops=1)

23.          

SubPlan (forForeign Scan)

24. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.19..0.19 rows=1 width=16) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Sort (cost=0.19..0.19 rows=1 width=16) (never executed)

  • Sort Key: scheduled."timestamp" DESC
26. 0.000 0.000 ↓ 0.0 0

CTE Scan on scheduled (cost=0.00..0.19 rows=1 width=16) (never executed)

  • Filter: (("timestamp" < db."timestamp") AND (request_id = db.ep_request_id))
27.          

CTE ranked_actions

28. 0.028 541.671 ↓ 12.0 36 1

WindowAgg (cost=247.69..247.70 rows=3 width=48) (actual time=541.655..541.671 rows=36 loops=1)

29. 0.027 541.643 ↓ 12.0 36 1

Sort (cost=247.69..247.69 rows=3 width=40) (actual time=541.640..541.643 rows=36 loops=1)

  • Sort Key: actions.date DESC
  • Sort Method: quicksort Memory: 27kB
30. 0.006 541.616 ↓ 12.0 36 1

Subquery Scan on actions (cost=247.67..247.69 rows=3 width=40) (actual time=541.604..541.616 rows=36 loops=1)

31. 0.005 541.610 ↓ 12.0 36 1

Unique (cost=247.67..247.68 rows=3 width=40) (actual time=541.603..541.610 rows=36 loops=1)

32. 0.040 541.605 ↓ 12.0 36 1

Sort (cost=247.67..247.67 rows=3 width=40) (actual time=541.603..541.605 rows=36 loops=1)

  • Sort Key: ('Call done'::text), (("*SELECT* 1".date)::timestamp with time zone)
  • Sort Method: quicksort Memory: 27kB
33. 0.006 541.565 ↓ 12.0 36 1

Append (cost=100.00..247.67 rows=3 width=40) (actual time=3.990..541.565 rows=36 loops=1)

34. 0.013 4.003 ↓ 35.0 35 1

Subquery Scan on *SELECT* 1 (cost=100.00..120.26 rows=1 width=40) (actual time=3.990..4.003 rows=35 loops=1)

35. 3.990 3.990 ↓ 35.0 35 1

Foreign Scan on requests r (cost=100.00..120.25 rows=1 width=40) (actual time=3.983..3.990 rows=35 loops=1)

36. 76.758 76.758 ↓ 0.0 0 1

Foreign Scan (cost=100.00..127.35 rows=1 width=40) (actual time=76.758..76.758 rows=0 loops=1)

  • Relations: (plato_api.request_no_show_declared_mentor ns1) LEFT JOIN (plato_api.request_no_show_declared_mentor ns2)
37. 0.010 460.798 ↑ 1.0 1 1

Nested Loop Anti Join (cost=0.00..0.06 rows=1 width=40) (actual time=460.796..460.798 rows=1 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: 2
38. 460.784 460.784 ↓ 2.0 2 1

CTE Scan on correct_request_event_declined cred (cost=0.00..0.02 rows=1 width=24) (actual time=460.783..460.784 rows=2 loops=1)

  • Filter: (date_part('day'::text, (request_call_scheduled_date - "timestamp")) >= '0'::double precision)
39. 0.004 0.004 ↑ 1.5 2 2

CTE Scan on correct_request_event_declined cred2 (cost=0.00..0.02 rows=3 width=24) (actual time=0.001..0.002 rows=2 loops=2)

40. 0.045 544.752 ↑ 8.4 8 1

Sort (cost=147.76..147.79 rows=67 width=100) (actual time=544.751..544.752 rows=8 loops=1)

  • Sort Key: u.first_name, u.last_name
  • Sort Method: quicksort Memory: 25kB
41. 0.007 544.707 ↑ 8.4 8 1

Nested Loop (cost=100.00..147.35 rows=67 width=100) (actual time=544.677..544.707 rows=8 loops=1)

42. 541.684 541.684 ↓ 8.0 8 1

CTE Scan on ranked_actions ra (cost=0.00..0.02 rows=1 width=32) (actual time=541.657..541.684 rows=8 loops=1)

  • Filter: (rank <= 8)
  • Rows Removed by Filter: 28
43. 3.016 3.016 ↑ 67.0 1 8

Foreign Scan (cost=100.00..147.13 rows=67 width=64) (actual time=0.377..0.377 rows=1 loops=8)

  • Relations: (plato_data.manager_users mu) INNER JOIN (plato_data.users u)
Planning time : 2.688 ms
Execution time : 1,250.440 ms