explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PGd6

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 3,731.584 ↓ 0.0 0 1

Sort (cost=90,637.96..90,638.87 rows=364 width=89) (actual time=3,731.584..3,731.584 rows=0 loops=1)

  • Sort Key: assessments.updated_at DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.004 3,731.557 ↓ 0.0 0 1

HashAggregate (cost=90,618.83..90,622.47 rows=364 width=89) (actual time=3,731.557..3,731.557 rows=0 loops=1)

  • Group Key: assessments.updated_at, assessments.id, assessments.owner_id, assessments.quiz_api_quiz_id, assessments.title, assessments.created_at, assessments.deleted_at, assessments.properties, true
3. 0.000 3,731.553 ↓ 0.0 0 1

Hash Left Join (cost=110.62..90,014.28 rows=26,869 width=89) (actual time=3,731.553..3,731.553 rows=0 loops=1)

  • Hash Cond: (deployments.id = deployment_availability_windows.deployment_id)
4. 1,118.439 3,731.553 ↓ 0.0 0 1

Nested Loop Left Join (cost=33.74..89,452.23 rows=6,661 width=96) (actual time=3,731.553..3,731.553 rows=0 loops=1)

  • Filter: ((dcs.course_section_id = teacher_enrollments.course_section_id) OR (dcs.* IS NULL))
  • Rows Removed by Filter: 13903826
5. 2.109 29.814 ↓ 60.6 3,270 1

Hash Join (cost=33.31..218.64 rows=54 width=112) (actual time=0.546..29.814 rows=3,270 loops=1)

  • Hash Cond: (deployed_assessments.assessment_id = assessments.id)
6. 1.377 27.301 ↓ 89.5 13,246 1

Nested Loop (cost=2.14..186.38 rows=148 width=24) (actual time=0.129..27.301 rows=13,246 loops=1)

  • Join Filter: (users.id = deployed_assessments.user_id)
7. 0.876 9.284 ↓ 94.5 2,080 1

Nested Loop (cost=1.71..167.83 rows=22 width=24) (actual time=0.070..9.284 rows=2,080 loops=1)

8. 0.246 2.168 ↓ 94.5 2,080 1

Nested Loop (cost=1.29..157.74 rows=22 width=16) (actual time=0.058..2.168 rows=2,080 loops=1)

9. 0.119 0.596 ↓ 102.0 102 1

Nested Loop (cost=0.85..149.09 rows=1 width=16) (actual time=0.049..0.596 rows=102 loops=1)

10. 0.171 0.171 ↓ 102.0 102 1

Index Scan using index_enrollments_on_user_id on enrollments teacher_enrollments (cost=0.43..144.64 rows=1 width=8) (actual time=0.028..0.171 rows=102 loops=1)

  • Index Cond: (user_id = 404873)
  • Filter: (active AND ((role)::text = 'teacher'::text))
11. 0.306 0.306 ↑ 1.0 1 102

Index Only Scan using course_sections_pkey on course_sections cs (cost=0.42..4.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=102)

  • Index Cond: (id = teacher_enrollments.course_section_id)
  • Heap Fetches: 0
12. 1.326 1.326 ↑ 6.9 20 102

Index Scan using index_enrollments_on_course_section_id on enrollments (cost=0.43..7.26 rows=138 width=16) (actual time=0.004..0.013 rows=20 loops=102)

  • Index Cond: (course_section_id = cs.id)
  • Filter: ((role)::text = 'student'::text)
  • Rows Removed by Filter: 1
13. 6.240 6.240 ↑ 1.0 1 2,080

Index Only Scan using users_pkey on users (cost=0.42..0.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2,080)

  • Index Cond: (id = enrollments.user_id)
  • Heap Fetches: 2080
14. 16.640 16.640 ↑ 2.2 6 2,080

Index Scan using index_deployed_assessments_on_user_id on deployed_assessments (cost=0.43..0.68 rows=13 width=24) (actual time=0.003..0.008 rows=6 loops=2,080)

  • Index Cond: (user_id = enrollments.user_id)
15. 0.021 0.404 ↑ 1.4 56 1

Hash (cost=30.15..30.15 rows=81 width=96) (actual time=0.404..0.404 rows=56 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
16. 0.111 0.383 ↑ 1.4 56 1

Hash Right Join (cost=9.61..30.15 rows=81 width=96) (actual time=0.230..0.383 rows=56 loops=1)

  • Hash Cond: (deployments.assessment_id = assessments.id)
  • Filter: ((NOT deployments.published) OR (deployments.published IS NULL))
  • Rows Removed by Filter: 398
17. 0.061 0.061 ↑ 1.0 437 1

Seq Scan on deployments (cost=0.00..15.33 rows=437 width=17) (actual time=0.002..0.061 rows=437 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 96
18. 0.024 0.211 ↑ 1.0 182 1

Hash (cost=7.33..7.33 rows=182 width=88) (actual time=0.211..0.211 rows=182 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
19. 0.187 0.187 ↑ 1.0 182 1

Seq Scan on assessments (cost=0.00..7.33 rows=182 width=88) (actual time=0.014..0.187 rows=182 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((title)::text ~~* '%%'::text) AND (owner_id <> 404873))
  • Rows Removed by Filter: 40
20. 2,583.300 2,583.300 ↑ 12.9 4,252 3,270

Index Scan using index_deployment_course_sections_on_deployment_id on deployment_course_sections dcs (cost=0.43..964.19 rows=55,063 width=80) (actual time=0.003..0.790 rows=4,252 loops=3,270)

  • Index Cond: (deployed_assessments.deployment_id = deployment_id)
21. 0.000 0.000 ↓ 0.0 0

Hash (cost=49.17..49.17 rows=2,217 width=8) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Seq Scan on deployment_availability_windows (cost=0.00..49.17 rows=2,217 width=8) (never executed)