explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TJrt

Settings
# exclusive inclusive rows x rows loops node
1. 16.506 1,095.430 ↑ 404.0 99 1

GroupAggregate (cost=23,051.02..24,341.02 rows=40,000 width=72) (actual time=1,066.189..1,095.430 rows=99 loops=1)

  • Group Key: (to_char((wgs.date)::timestamp with time zone, 'yyyy-mm'::text)), wfcip.coursesuggestion
2.          

CTE with_user_periods_inside_team

3. 0.576 0.576 ↓ 2.5 254 1

Function Scan on json_to_recordset (cost=0.00..1.00 rows=100 width=24) (actual time=0.558..0.576 rows=254 loops=1)

4.          

CTE with_generate_series

5. 0.125 0.740 ↑ 19.6 51 1

Aggregate (cost=3.25..8.29 rows=1,000 width=4) (actual time=0.671..0.740 rows=51 loops=1)

6. 0.615 0.615 ↓ 2.5 254 1

CTE Scan on with_user_periods_inside_team (cost=0.00..2.00 rows=100 width=16) (actual time=0.559..0.615 rows=254 loops=1)

7.          

CTE with_followed_courses_in_progress

8. 0.810 5.164 ↓ 13.5 2,791 1

Nested Loop (cost=0.43..5,400.51 rows=207 width=44) (actual time=0.014..5.164 rows=2,791 loops=1)

9. 0.036 0.036 ↓ 2.5 254 1

CTE Scan on with_user_periods_inside_team wupit (cost=0.00..2.00 rows=100 width=20) (actual time=0.000..0.036 rows=254 loops=1)

10. 4.318 4.318 ↓ 5.5 11 254

Index Scan using idx_85edda85a76ed395 on claire_followed_course_history cfch (cost=0.43..53.96 rows=2 width=16) (actual time=0.006..0.017 rows=11 loops=254)

  • Index Cond: (user_id = wupit.user_id)
  • Filter: ((unfollowed_at IS NULL) AND (followed_at >= wupit.team_joining_date) AND (followed_at <= COALESCE(wupit.team_leaving_date, 'infinity'::timestamp without time zone)))
  • Rows Removed by Filter: 7
11. 861.750 1,078.924 ↑ 1.2 57,515 1

Sort (cost=17,641.21..17,813.71 rows=69,000 width=128) (actual time=1,066.179..1,078.924 rows=57,515 loops=1)

  • Sort Key: (to_char((wgs.date)::timestamp with time zone, 'yyyy-mm'::text)), wfcip.coursesuggestion
  • Sort Method: external sort Disk: 3,568kB
12. 171.823 217.174 ↑ 1.2 57,515 1

Nested Loop Left Join (cost=0.00..7,612.07 rows=69,000 width=128) (actual time=1.285..217.174 rows=57,515 loops=1)

  • Join Filter: ((wfcip.followed_at)::date <= ((date_trunc('month'::text, (wgs.date)::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval))
  • Rows Removed by Join Filter: 84,826
13. 0.777 0.777 ↑ 19.6 51 1

CTE Scan on with_generate_series wgs (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.672..0.777 rows=51 loops=1)

14. 44.574 44.574 ↓ 13.5 2,791 51

CTE Scan on with_followed_courses_in_progress wfcip (cost=0.00..4.14 rows=207 width=104) (actual time=0.001..0.874 rows=2,791 loops=51)

Planning time : 0.240 ms
Execution time : 1,097.822 ms