explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WNnm

Settings
# exclusive inclusive rows x rows loops node
1. 7.670 13,679.405 ↑ 348.5 99 1

GroupAggregate (cost=39,679.62..40,542.12 rows=34,500 width=72) (actual time=13,665.624..13,679.405 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. 1.020 1.020 ↓ 2.5 254 1

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

4.          

CTE with_generate_series

5. 0.205 1.303 ↑ 19.6 51 1

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

6. 1.098 1.098 ↓ 2.5 254 1

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

7.          

CTE with_completed_courses

8. 0.082 38.947 ↓ 45.0 225 1

Unique (cost=6,863.39..6,863.42 rows=5 width=36) (actual time=38.790..38.947 rows=225 loops=1)

9. 0.343 38.865 ↓ 193.4 967 1

Sort (cost=6,863.39..6,863.40 rows=5 width=36) (actual time=38.790..38.865 rows=967 loops=1)

  • Sort Key: t.reference_id
  • Sort Method: quicksort Memory: 70kB
10. 0.605 38.522 ↓ 193.4 967 1

Subquery Scan on t (cost=6,863.22..6,863.33 rows=5 width=36) (actual time=37.771..38.522 rows=967 loops=1)

11. 0.752 37.917 ↓ 193.4 967 1

HashAggregate (cost=6,863.22..6,863.27 rows=5 width=12) (actual time=37.761..37.917 rows=967 loops=1)

  • Group Key: ccc.reference_course_id, ccc.completed_at
12. 0.180 37.165 ↓ 256.0 1,280 1

Append (cost=0.85..6,863.19 rows=5 width=12) (actual time=0.026..37.165 rows=1,280 loops=1)

13. 0.156 4.250 ↓ 154.0 154 1

Nested Loop (cost=0.85..1,452.19 rows=1 width=12) (actual time=0.025..4.250 rows=154 loops=1)

  • Join Filter: ((cfch.followed_at >= wupit.team_joining_date) AND (cfch.followed_at <= COALESCE(wupit.team_leaving_date, 'infinity'::timestamp without time zone)) AND (wupit.user_id = cfch.user_id))
  • Rows Removed by Join Filter: 1
14. 0.069 0.882 ↓ 4.9 146 1

Nested Loop (cost=0.42..1,368.73 rows=30 width=36) (actual time=0.011..0.882 rows=146 loops=1)

15. 0.051 0.051 ↓ 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.051 rows=254 loops=1)

16. 0.762 0.762 ↑ 1.0 1 254

Index Scan using idx_48cd04a5a76ed395 on claire_completed_course ccc (cost=0.42..13.66 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=254)

  • Index Cond: (user_id = wupit.user_id)
  • Filter: ((completed_at >= wupit.team_joining_date) AND (completed_at <= COALESCE(wupit.team_leaving_date, 'infinity'::timestamp without time zone)))
  • Rows Removed by Filter: 0
17. 3.212 3.212 ↑ 1.0 1 146

Index Scan using idx_85edda85a76ed395 on claire_followed_course_history cfch (cost=0.43..2.76 rows=1 width=16) (actual time=0.014..0.022 rows=1 loops=146)

  • Index Cond: (user_id = ccc.user_id)
  • Filter: (ccc.reference_course_id = reference_id)
  • Rows Removed by Filter: 40
18. 0.389 32.735 ↓ 281.5 1,126 1

Nested Loop (cost=0.86..5,410.96 rows=4 width=12) (actual time=0.020..32.735 rows=1,126 loops=1)

  • Join Filter: ((cfch_1.followed_at >= wupit2.team_joining_date) AND (cfch_1.followed_at <= COALESCE(wupit2.team_leaving_date, 'infinity'::timestamp without time zone)) AND (wupit2.user_id = cfch_1.user_id))
  • Rows Removed by Join Filter: 111
19. 0.312 2.646 ↓ 6.9 1,188 1

Nested Loop (cost=0.43..5,230.19 rows=173 width=36) (actual time=0.009..2.646 rows=1,188 loops=1)

20. 0.048 0.048 ↓ 2.5 254 1

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

21. 2.286 2.286 ↓ 2.5 5 254

Index Scan using idx_2553bf7aa76ed395 on claire_user_course_result cucr (cost=0.43..52.26 rows=2 width=16) (actual time=0.004..0.009 rows=5 loops=254)

  • Index Cond: (user_id = wupit2.user_id)
  • Filter: (passed AND (created_at >= wupit2.team_joining_date) AND (created_at <= COALESCE(wupit2.team_leaving_date, 'infinity'::timestamp without time zone)))
  • Rows Removed by Filter: 3
22. 29.700 29.700 ↑ 1.0 1 1,188

Index Scan using idx_85edda85a76ed395 on claire_followed_course_history cfch_1 (cost=0.43..1.03 rows=1 width=16) (actual time=0.016..0.025 rows=1 loops=1,188)

  • Index Cond: (user_id = cucr.user_id)
  • Filter: (cucr.reference_id = reference_id)
  • Rows Removed by Filter: 42
23.          

CTE with_followed_courses_in_progress

24. 0.498 4.082 ↓ 13.5 2,791 1

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

25. 0.028 0.028 ↓ 2.5 254 1

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

26. 3.556 3.556 ↓ 5.5 11 254

Index Scan using idx_85edda85a76ed395 on claire_followed_course_history cfch_2 (cost=0.43..53.96 rows=2 width=16) (actual time=0.004..0.014 rows=11 loops=254)

  • Index Cond: (user_id = wupit_1.user_id)
  • Filter: ((unfollowed_at IS NULL) AND (followed_at >= wupit_1.team_joining_date) AND (followed_at <= COALESCE(wupit_1.team_leaving_date, 'infinity'::timestamp without time zone)))
  • Rows Removed by Filter: 7
27. 757.686 13,671.735 ↓ 1.6 56,167 1

Sort (cost=27,406.39..27,492.64 rows=34,500 width=132) (actual time=13,665.617..13,671.735 rows=56,167 loops=1)

  • Sort Key: (to_char((wgs.date)::timestamp with time zone, 'yyyy-mm'::text)), wfcip.coursesuggestion
  • Sort Method: external sort Disk: 3,480kB
28. 264.308 12,914.049 ↓ 1.6 56,167 1

Nested Loop Left Join (cost=0.00..22,447.07 rows=34,500 width=132) (actual time=41.321..12,914.049 rows=56,167 loops=1)

  • Join Filter: (((wfcip.followed_at)::date <= ((date_trunc('month'::text, (wgs.date)::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval)) AND (NOT (SubPlan 5)))
  • Rows Removed by Join Filter: 86,174
29. 1.375 1.375 ↑ 19.6 51 1

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

30. 52.581 52.581 ↓ 13.5 2,791 51

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

31.          

SubPlan (for Nested Loop Left Join)

32. 12,595.785 12,595.785 ↓ 7.0 7 57,515

CTE Scan on with_completed_courses (cost=0.00..0.14 rows=1 width=4) (actual time=0.078..0.219 rows=7 loops=57,515)

  • Filter: (to_char((wgs.date)::timestamp with time zone, 'yyyy-mm'::text) = date)
  • Rows Removed by Filter: 216
Planning time : 1.782 ms
Execution time : 13,693.869 ms