explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uSVU

Settings
# exclusive inclusive rows x rows loops node
1. 0.414 7.351 ↑ 1.0 1 1

GroupAggregate (cost=537.32..552.63 rows=1 width=215) (actual time=7.350..7.351 rows=1 loops=1)

  • Group Key: courses.id, pc.id, a.first_name, a.last_name, ccp.progress, ccp.assigned, ccp.complete
2. 0.484 6.730 ↓ 414.0 414 1

Sort (cost=537.32..537.33 rows=1 width=184) (actual time=6.646..6.730 rows=414 loops=1)

  • Sort Key: pc.id, a.first_name, a.last_name, ccp.progress, ccp.assigned, ccp.complete
  • Sort Method: quicksort Memory: 134kB
3. 0.467 6.246 ↓ 414.0 414 1

Nested Loop Left Join (cost=66.16..537.31 rows=1 width=184) (actual time=0.123..6.246 rows=414 loops=1)

  • Join Filter: (lc.course_id = courses.id)
4. 0.416 2.881 ↓ 414.0 414 1

Nested Loop Left Join (cost=57.10..524.22 rows=1 width=167) (actual time=0.104..2.881 rows=414 loops=1)

  • Join Filter: (pc.published_course_id = courses.id)
5. 0.296 1.637 ↓ 414.0 414 1

Nested Loop Left Join (cost=56.82..515.91 rows=1 width=151) (actual time=0.098..1.637 rows=414 loops=1)

6. 0.220 0.513 ↓ 414.0 414 1

Nested Loop Left Join (cost=56.54..514.50 rows=1 width=151) (actual time=0.089..0.513 rows=414 loops=1)

  • Join Filter: (cc.course_id = courses.id)
7. 0.004 0.035 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.97..25.05 rows=1 width=134) (actual time=0.033..0.035 rows=1 loops=1)

  • Join Filter: (ccp.course_id = courses.id)
8. 0.003 0.022 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.56..16.61 rows=1 width=128) (actual time=0.021..0.022 rows=1 loops=1)

9. 0.014 0.014 ↑ 1.0 1 1

Index Scan using courses_organisation_id_id_idx on courses (cost=0.28..8.30 rows=1 width=116) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: ((organisation_id = 'cd6283e1-10e2-4189-ac9a-fcd15c8f1e30'::uuid) AND (id = '748d08d8-4d6b-4533-930e-d3f1ee52ef3d'::uuid))
10. 0.005 0.005 ↑ 1.0 1 1

Index Scan using profiles_pkey on profiles a (cost=0.28..8.29 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (id = courses.author_id)
11. 0.009 0.009 ↑ 1.0 1 1

Index Scan using course_completions_course_id_profile_id_key on course_completions ccp (cost=0.41..8.43 rows=1 width=22) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: ((course_id = '748d08d8-4d6b-4533-930e-d3f1ee52ef3d'::uuid) AND (profile_id = '5a71c141-863f-4123-81bd-c6126e2e8c6f'::uuid))
12. 0.211 0.258 ↓ 2.2 414 1

Bitmap Heap Scan on course_completions cc (cost=55.57..487.06 rows=191 width=33) (actual time=0.053..0.258 rows=414 loops=1)

  • Recheck Cond: (course_id = '748d08d8-4d6b-4533-930e-d3f1ee52ef3d'::uuid)
  • Filter: assigned
  • Heap Blocks: exact=19
13. 0.047 0.047 ↓ 1.6 649 1

Bitmap Index Scan on course_completions_course_id_profile_id_key (cost=0.00..55.52 rows=415 width=0) (actual time=0.047..0.047 rows=649 loops=1)

  • Index Cond: (course_id = '748d08d8-4d6b-4533-930e-d3f1ee52ef3d'::uuid)
14. 0.828 0.828 ↑ 1.0 1 414

Index Scan using profiles_uuid_key on profiles p (cost=0.28..1.40 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=414)

  • Index Cond: (id = cc.profile_id)
  • Filter: (NOT disabled)
15. 0.828 0.828 ↑ 1.0 1 414

Index Scan using courses_published_course_id_idx2 on courses pc (cost=0.28..8.29 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=414)

  • Index Cond: (published_course_id = '748d08d8-4d6b-4533-930e-d3f1ee52ef3d'::uuid)
16. 0.414 2.898 ↓ 0.0 0 414

Bitmap Heap Scan on lesson_completions lc (cost=9.07..13.08 rows=1 width=33) (actual time=0.007..0.007 rows=0 loops=414)

  • Recheck Cond: ((profile_id = '5a71c141-863f-4123-81bd-c6126e2e8c6f'::uuid) AND (course_id = '748d08d8-4d6b-4533-930e-d3f1ee52ef3d'::uuid))
17. 0.828 2.484 ↓ 0.0 0 414

BitmapAnd (cost=9.07..9.07 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=414)

18. 0.828 0.828 ↑ 1.0 15 414

Bitmap Index Scan on lesson_completions_profile_id_idx (cost=0.00..4.39 rows=15 width=0) (actual time=0.002..0.002 rows=15 loops=414)

  • Index Cond: (profile_id = '5a71c141-863f-4123-81bd-c6126e2e8c6f'::uuid)
19. 0.828 0.828 ↑ 1.0 20 414

Bitmap Index Scan on lesson_completions_course_id_idx (cost=0.00..4.43 rows=20 width=0) (actual time=0.002..0.002 rows=20 loops=414)

  • Index Cond: (course_id = '748d08d8-4d6b-4533-930e-d3f1ee52ef3d'::uuid)
20.          

SubPlan (for GroupAggregate)

21. 0.067 0.207 ↑ 1.0 1 1

Aggregate (cost=15.25..15.26 rows=1 width=8) (actual time=0.207..0.207 rows=1 loops=1)

22. 0.122 0.140 ↓ 100.0 300 1

Bitmap Heap Scan on lessons (cost=4.31..15.24 rows=3 width=16) (actual time=0.023..0.140 rows=300 loops=1)

  • Recheck Cond: (course_id = courses.id)
  • Heap Blocks: exact=14
23. 0.018 0.018 ↓ 100.0 300 1

Bitmap Index Scan on lessons_course_id_idx (cost=0.00..4.31 rows=3 width=0) (actual time=0.017..0.018 rows=300 loops=1)

  • Index Cond: (course_id = courses.id)
Planning time : 1.105 ms
Execution time : 7.495 ms