explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wRZs

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 39,696.526 ↑ 36.4 10 1

Sort (cost=719,789.72..719,790.63 rows=364 width=89) (actual time=39,696.525..39,696.526 rows=10 loops=1)

  • Sort Key: assessments.updated_at DESC
  • Sort Method: quicksort Memory: 26kB
2. 45.638 39,696.509 ↑ 36.4 10 1

HashAggregate (cost=719,770.60..719,774.24 rows=364 width=89) (actual time=39,696.506..39,696.509 rows=10 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. 37,832.308 39,650.871 ↑ 1.3 98,938 1

Hash Left Join (cost=242,727.41..716,876.99 rows=128,605 width=89) (actual time=2,702.925..39,650.871 rows=98,938 loops=1)

  • Hash Cond: (deployed_assessments.deployment_id = dcs.deployment_id)
  • Filter: ((dcs.course_section_id = teacher_enrollments.course_section_id) OR (dcs.* IS NULL))
  • Rows Removed by Filter: 284708962
4. 25.949 68.000 ↓ 130.3 136,036 1

Hash Join (cost=221.73..293.54 rows=1,044 width=104) (actual time=41.700..68.000 rows=136,036 loops=1)

  • Hash Cond: (deployment_availability_windows.deployment_id = deployments.id)
5. 0.399 0.399 ↓ 1.0 1,781 1

Seq Scan on deployment_availability_windows (cost=0.00..54.71 rows=1,780 width=8) (actual time=0.010..0.399 rows=1,781 loops=1)

  • Filter: (ends_at <= '2019-01-07 20:11:49.591944'::timestamp without time zone)
  • Rows Removed by Filter: 485
6. 9.727 41.652 ↓ 100.4 32,316 1

Hash (cost=217.70..217.70 rows=322 width=112) (actual time=41.652..41.652 rows=32,316 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3841kB
7. 5.821 31.925 ↓ 100.4 32,316 1

Hash Join (cost=199.26..217.70 rows=322 width=112) (actual time=26.006..31.925 rows=32,316 loops=1)

  • Hash Cond: (deployments.assessment_id = assessments.id)
8. 0.119 0.119 ↓ 1.2 398 1

Seq Scan on deployments (cost=0.00..15.33 rows=338 width=16) (actual time=0.005..0.119 rows=398 loops=1)

  • Filter: ((deleted_at IS NULL) AND published)
  • Rows Removed by Filter: 135
9. 4.056 25.985 ↓ 109.5 13,246 1

Hash (cost=197.75..197.75 rows=121 width=112) (actual time=25.985..25.985 rows=13,246 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2087kB
10. 2.993 21.929 ↓ 109.5 13,246 1

Hash Join (cost=11.75..197.75 rows=121 width=112) (actual time=0.318..21.929 rows=13,246 loops=1)

  • Hash Cond: (deployed_assessments.assessment_id = assessments.id)
11. 2.173 18.724 ↓ 89.5 13,246 1

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

  • Join Filter: (users.id = deployed_assessments.user_id)
12. 0.628 6.151 ↓ 94.5 2,080 1

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

13. 0.202 1.363 ↓ 94.5 2,080 1

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

14. 0.065 0.243 ↓ 102.0 102 1

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

15. 0.076 0.076 ↓ 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.013..0.076 rows=102 loops=1)

  • Index Cond: (user_id = 404873)
  • Filter: (active AND ((role)::text = 'teacher'::text))
16. 0.102 0.102 ↑ 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.001..0.001 rows=1 loops=102)

  • Index Cond: (id = teacher_enrollments.course_section_id)
  • Heap Fetches: 0
17. 0.918 0.918 ↑ 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.002..0.009 rows=20 loops=102)

  • Index Cond: (course_section_id = cs.id)
  • Filter: ((role)::text = 'student'::text)
  • Rows Removed by Filter: 1
18. 4.160 4.160 ↑ 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.002..0.002 rows=1 loops=2,080)

  • Index Cond: (id = enrollments.user_id)
  • Heap Fetches: 2080
19. 10.400 10.400 ↑ 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.002..0.005 rows=6 loops=2,080)

  • Index Cond: (user_id = enrollments.user_id)
20. 0.043 0.212 ↑ 1.0 182 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
21. 0.169 0.169 ↑ 1.0 182 1

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

  • Filter: ((deleted_at IS NULL) AND ((title)::text ~~* '%%'::text) AND (owner_id <> 404873))
  • Rows Removed by Filter: 40
22. 1,020.640 1,750.563 ↓ 1.0 4,878,931 1

Hash (cost=123,195.08..123,195.08 rows=4,735,408 width=80) (actual time=1,750.563..1,750.563 rows=4,878,931 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 2048 (originally 256) Memory Usage: 11960kB
23. 729.923 729.923 ↓ 1.0 4,878,931 1

Seq Scan on deployment_course_sections dcs (cost=0.00..123,195.08 rows=4,735,408 width=80) (actual time=0.011..729.923 rows=4,878,931 loops=1)