explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OsTZ

Settings
# exclusive inclusive rows x rows loops node
1. 27.426 374.159 ↑ 36.2 10 1

Unique (cost=1,694.78..1,711.65 rows=362 width=91) (actual time=276.840..374.159 rows=10 loops=1)

2. 229.519 346.733 ↓ 131.9 98,938 1

Sort (cost=1,694.78..1,696.65 rows=750 width=91) (actual time=276.839..346.733 rows=98,938 loops=1)

  • Sort Key: assessments.updated_at DESC, assessments.id, assessments.owner_id, assessments.quiz_api_quiz_id, assessments.title, assessments.created_at, assessments.deleted_at, assessments.properties
  • Sort Method: external merge Disk: 8936kB
3. 16.418 117.214 ↓ 131.9 98,938 1

Hash Join (cost=116.71..1,658.96 rows=750 width=91) (actual time=1.845..117.214 rows=98,938 loops=1)

  • Hash Cond: (deployed_assessments.assessment_id = deployments.assessment_id)
4. 1.874 99.312 ↓ 26.2 2,832 1

Nested Loop (cost=2.14..1,526.19 rows=108 width=8) (actual time=0.354..99.312 rows=2,832 loops=1)

  • Join Filter: ((users.id = deployed_assessments.user_id) AND ((SubPlan 1) OR (NOT (SubPlan 2))))
  • Rows Removed by Join Filter: 10414
5. 0.000 5.482 ↓ 99.0 2,080 1

Nested Loop (cost=1.71..163.03 rows=21 width=24) (actual time=0.033..5.482 rows=2,080 loops=1)

6. 0.296 1.508 ↓ 99.0 2,080 1

Nested Loop (cost=1.29..153.56 rows=21 width=16) (actual time=0.027..1.508 rows=2,080 loops=1)

7. 0.001 0.294 ↓ 102.0 102 1

Nested Loop (cost=0.85..145.05 rows=1 width=16) (actual time=0.019..0.294 rows=102 loops=1)

8. 0.089 0.089 ↓ 102.0 102 1

Index Scan using index_enrollments_on_user_id on enrollments teacher_enrollments (cost=0.43..140.60 rows=1 width=8) (actual time=0.013..0.089 rows=102 loops=1)

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

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

Index Scan using index_enrollments_on_course_section_id on enrollments (cost=0.43..7.13 rows=138 width=16) (actual time=0.003..0.009 rows=20 loops=102)

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

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

  • Index Cond: (id = enrollments.user_id)
  • Heap Fetches: 0
12. 12.480 12.480 ↑ 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.006 rows=6 loops=2,080)

  • Index Cond: (user_id = enrollments.user_id)
13.          

SubPlan (forNested Loop)

14. 13.246 13.246 ↓ 0.0 0 13,246

Index Only Scan using unique_index_on_deployment_course_sections on deployment_course_sections dcs (cost=0.43..4.45 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=13,246)

  • Index Cond: ((deployment_id = deployed_assessments.deployment_id) AND (course_section_id = teacher_enrollments.course_section_id))
  • Heap Fetches: 0
15. 66.230 66.230 ↑ 33,515.0 1 13,246

Index Only Scan using index_deployment_course_sections_on_deployment_id on deployment_course_sections dcs_1 (cost=0.43..1,454.94 rows=33,515 width=0) (actual time=0.005..0.005 rows=1 loops=13,246)

  • Index Cond: (deployment_id = deployed_assessments.deployment_id)
  • Heap Fetches: 0
16. 0.310 1.484 ↓ 1.8 1,613 1

Hash (cost=103.37..103.37 rows=896 width=98) (actual time=1.484..1.484 rows=1,613 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 219kB
17. 0.392 1.174 ↓ 1.8 1,613 1

Hash Join (cost=32.40..103.37 rows=896 width=98) (actual time=0.460..1.174 rows=1,613 loops=1)

  • Hash Cond: (deployment_availability_windows.deployment_id = deployments.id)
18. 0.332 0.332 ↑ 1.0 1,781 1

Seq Scan on deployment_availability_windows (cost=0.00..55.33 rows=1,783 width=8) (actual time=0.006..0.332 rows=1,781 loops=1)

  • Filter: (ends_at <= '2019-01-07 20:11:49.591944'::timestamp without time zone)
  • Rows Removed by Filter: 485
19. 0.072 0.450 ↓ 1.4 398 1

Hash (cost=28.95..28.95 rows=276 width=106) (actual time=0.450..0.450 rows=398 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 64kB
20. 0.096 0.378 ↓ 1.4 398 1

Hash Join (cost=9.59..28.95 rows=276 width=106) (actual time=0.201..0.378 rows=398 loops=1)

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

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

  • Filter: ((deleted_at IS NULL) AND published)
  • Rows Removed by Filter: 135
22. 0.015 0.195 ↓ 1.0 182 1

Hash (cost=7.33..7.33 rows=181 width=90) (actual time=0.195..0.195 rows=182 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
23. 0.180 0.180 ↓ 1.0 182 1

Seq Scan on assessments (cost=0.00..7.33 rows=181 width=90) (actual time=0.009..0.180 rows=182 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((title)::text ~~* '%%'::text) AND (owner_id <> 404873))
  • Rows Removed by Filter: 40