explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kOh6 : bad query, c202, slave, 2020-02-06 15:12:41.458344+00

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.049 126,554.494 ↓ 59.0 59 1

Sort (cost=45,545.78..45,545.78 rows=1 width=32) (actual time=126,554.491..126,554.494 rows=59 loops=1)

  • Sort Key: assignments.created_at
  • Sort Method: quicksort Memory: 29kB
2. 45.060 126,554.445 ↓ 59.0 59 1

HashAggregate (cost=45,545.75..45,545.77 rows=1 width=32) (actual time=126,554.431..126,554.445 rows=59 loops=1)

  • Group Key: assignments.id
3. 116.507 126,509.385 ↓ 138,555.0 138,555 1

Nested Loop (cost=1,883.24..45,545.74 rows=1 width=32) (actual time=1,573.322..126,509.385 rows=138,555 loops=1)

4. 16.690 125,977.282 ↓ 138,532.0 138,532 1

Nested Loop (cost=1,882.81..45,543.45 rows=1 width=40) (actual time=1,573.279..125,977.282 rows=138,532 loops=1)

5. 0.894 0.894 ↓ 4.2 102 1

Index Scan using index_assignments_on_context_id_and_context_type on assignments (cost=0.43..177.44 rows=24 width=16) (actual time=0.075..0.894 rows=102 loops=1)

  • Index Cond: ((context_id = 26348) AND ((context_type)::text = 'Course'::text))
  • Filter: ((workflow_state)::text = 'published'::text)
6. 475.320 125,959.698 ↓ 1,358.0 1,358 102

Bitmap Heap Scan on submissions (cost=1,882.38..1,890.24 rows=1 width=32) (actual time=1,230.482..1,234.899 rows=1,358 loops=102)

  • Recheck Cond: ((assignment_id = assignments.id) AND (((score IS NOT NULL) AND ((workflow_state)::text = 'graded'::text)) OR excused))
  • Filter: ((excused IS NOT TRUE) AND (score IS NOT NULL) AND ((workflow_state)::text <> 'deleted'::text) AND ((workflow_state)::text <> 'deleted'::text) AND ((workflow_state)::text = 'graded'::text))
  • Heap Blocks: exact=137128
7. 470.526 125,484.378 ↓ 0.0 0 102

BitmapAnd (cost=1,882.38..1,882.38 rows=7 width=0) (actual time=1,230.239..1,230.239 rows=0 loops=102)

8. 160.038 160.038 ↓ 1.3 12,009 102

Bitmap Index Scan on index_submissions_on_assignment_id_and_submission_type (cost=0.00..115.82 rows=9,500 width=0) (actual time=1.569..1.569 rows=12,009 loops=102)

  • Index Cond: (assignment_id = assignments.id)
9. 124,853.814 124,853.814 ↓ 39.1 4,695,729 102

Bitmap Index Scan on index_submissions_graded_or_excused_on_user_id (cost=0.00..1,763.94 rows=119,942 width=0) (actual time=1,224.057..1,224.057 rows=4,695,729 loops=102)

10. 415.596 415.596 ↑ 1.0 1 138,532

Index Scan using index_enrollments_on_course_id_and_user_id on enrollments (cost=0.43..2.28 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=138,532)

  • Index Cond: ((course_id = 26348) AND (user_id = submissions.user_id))
  • Filter: (((type)::text = 'StudentEnrollment'::text) AND ((workflow_state)::text <> ALL ('{rejected,completed,deleted,inactive}'::text[])))
Planning time : 5.475 ms
Execution time : 126,555.190 ms