explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XTY3O

Settings
# exclusive inclusive rows x rows loops node
1. 0.441 128.790 ↓ 122.5 245 1

Sort (cost=3,249.87..3,249.87 rows=2 width=217) (actual time=128.778..128.790 rows=245 loops=1)

  • Sort Key: s.last_name, s.first_name
  • Sort Method: quicksort Memory: 90kB
2. 0.356 128.349 ↓ 122.5 245 1

Hash Join (cost=3,086.23..3,249.86 rows=2 width=217) (actual time=57.173..128.349 rows=245 loops=1)

  • Hash Cond: ((tr.sid)::text = (students_temp.sid)::text)
3. 0.157 126.711 ↓ 122.5 245 1

Nested Loop Left Join (cost=2,830.63..2,951.79 rows=2 width=97) (actual time=55.872..126.711 rows=245 loops=1)

4. 0.156 123.369 ↓ 245.0 245 1

Merge Join (cost=2,826.02..2,918.76 rows=1 width=81) (actual time=55.827..123.369 rows=245 loops=1)

  • Merge Cond: (tr.id = tr_1.id)
5. 38.713 84.640 ↓ 245.0 245 1

GroupAggregate (cost=2,019.76..2,020.54 rows=1 width=103) (actual time=45.163..84.640 rows=245 loops=1)

  • Group Key: tr.id, t.gradingscale
6.          

Initplan (for GroupAggregate)

7. 0.021 0.021 ↑ 1.0 1 1

Index Scan using test_section_pkey on test_section (cost=0.42..8.44 rows=1 width=16) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: (id = '27e82432-e8ab-11ea-bd70-f899b5ea795e'::uuid)
8. 5.846 45.906 ↓ 9,310.0 9,310 1

Sort (cost=2,011.32..2,011.33 rows=1 width=106) (actual time=44.838..45.906 rows=9,310 loops=1)

  • Sort Key: tr.id, t.gradingscale
  • Sort Method: quicksort Memory: 1,694kB
9. 11.448 40.060 ↓ 9,310.0 9,310 1

Nested Loop (cost=7.58..2,011.31 rows=1 width=106) (actual time=0.135..40.060 rows=9,310 loops=1)

10. 2.006 9.080 ↓ 63.8 9,766 1

Nested Loop (cost=6.88..677.92 rows=153 width=98) (actual time=0.113..9.080 rows=9,766 loops=1)

11. 0.016 0.234 ↓ 38.0 38 1

Nested Loop (cost=1.26..67.21 rows=1 width=51) (actual time=0.050..0.234 rows=38 loops=1)

12. 0.004 0.031 ↑ 1.0 1 1

Nested Loop (cost=0.83..16.87 rows=1 width=64) (actual time=0.029..0.031 rows=1 loops=1)

13. 0.017 0.017 ↑ 1.0 1 1

Index Only Scan using test_administration_test_id on test_administration ta (cost=0.42..8.44 rows=1 width=32) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (id = '22add6e2-e8ab-11ea-87e4-837e6f2587b0'::uuid)
  • Heap Fetches: 1
14. 0.010 0.010 ↑ 1.0 1 1

Index Scan using test_wrong_answer on test t (cost=0.42..8.44 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (id = ta.test_id)
15. 0.187 0.187 ↓ 12.7 38 1

Index Scan using question_order_id on question q (cost=0.43..50.31 rows=3 width=35) (actual time=0.020..0.187 rows=38 loops=1)

  • Index Cond: ((order_number = ANY ('{83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120}'::integer[])) AND (test_id = t.id))
16. 6.080 6.840 ↓ 1.7 257 38

Bitmap Heap Scan on test_registration tr (cost=5.62..609.18 rows=153 width=79) (actual time=0.070..0.180 rows=257 loops=38)

  • Recheck Cond: (test_administration_id = '22add6e2-e8ab-11ea-87e4-837e6f2587b0'::uuid)
  • Heap Blocks: exact=9,614
17. 0.760 0.760 ↓ 1.7 257 38

Bitmap Index Scan on test_registration_test_administration_id (cost=0.00..5.58 rows=153 width=0) (actual time=0.020..0.020 rows=257 loops=38)

  • Index Cond: (test_administration_id = '22add6e2-e8ab-11ea-87e4-837e6f2587b0'::uuid)
18. 19.532 19.532 ↑ 1.0 1 9,766

Index Scan using test_registration_result_pkey on test_registration_result r (cost=0.70..8.71 rows=1 width=57) (actual time=0.002..0.002 rows=1 loops=9,766)

  • Index Cond: ((question_id = q.id) AND (test_registration_id = tr.id))
19. 27.005 38.573 ↓ 1.7 257 1

GroupAggregate (cost=806.26..896.28 rows=153 width=40) (actual time=10.659..38.573 rows=257 loops=1)

  • Group Key: tr_1.id
20. 4.231 11.568 ↓ 57.1 9,766 1

Sort (cost=806.26..806.69 rows=171 width=27) (actual time=10.456..11.568 rows=9,766 loops=1)

  • Sort Key: tr_1.id
  • Sort Method: quicksort Memory: 1,147kB
21. 1.056 7.337 ↓ 57.1 9,766 1

Nested Loop (cost=6.47..799.92 rows=171 width=27) (actual time=0.102..7.337 rows=9,766 loops=1)

22. 0.017 0.201 ↓ 38.0 38 1

Nested Loop (cost=0.85..189.21 rows=1 width=27) (actual time=0.034..0.201 rows=38 loops=1)

23. 0.014 0.014 ↑ 1.0 1 1

Index Only Scan using test_administration_test_id on test_administration ta_1 (cost=0.42..8.44 rows=1 width=32) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (id = '22add6e2-e8ab-11ea-87e4-837e6f2587b0'::uuid)
  • Heap Fetches: 1
24. 0.170 0.170 ↓ 12.7 38 1

Index Scan using question_order_id on question q_1 (cost=0.43..180.75 rows=3 width=27) (actual time=0.018..0.170 rows=38 loops=1)

  • Index Cond: ((order_number = ANY ('{83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120}'::integer[])) AND (test_id = ta_1.test_id))
25. 5.396 6.080 ↓ 1.7 257 38

Bitmap Heap Scan on test_registration tr_1 (cost=5.62..609.18 rows=153 width=32) (actual time=0.061..0.160 rows=257 loops=38)

  • Recheck Cond: (test_administration_id = '22add6e2-e8ab-11ea-87e4-837e6f2587b0'::uuid)
  • Heap Blocks: exact=9,614
26. 0.684 0.684 ↓ 1.7 257 38

Bitmap Index Scan on test_registration_test_administration_id (cost=0.00..5.58 rows=153 width=0) (actual time=0.018..0.018 rows=257 loops=38)

  • Index Cond: (test_administration_id = '22add6e2-e8ab-11ea-87e4-837e6f2587b0'::uuid)
27. 0.245 3.185 ↑ 2.0 1 245

Nested Loop (cost=4.61..33.01 rows=2 width=25) (actual time=0.013..0.013 rows=1 loops=245)

28. 0.490 2.450 ↑ 7.0 1 245

Bitmap Heap Scan on students_full s (cost=4.48..31.82 rows=7 width=29) (actual time=0.010..0.010 rows=1 loops=245)

  • Recheck Cond: ((sid)::text = (tr.sid)::text)
  • Heap Blocks: exact=245
29. 1.960 1.960 ↑ 7.0 1 245

Bitmap Index Scan on ix_students_full_sid_school_year (cost=0.00..4.48 rows=7 width=0) (actual time=0.008..0.008 rows=1 loops=245)

  • Index Cond: ((sid)::text = (tr.sid)::text)
30. 0.490 0.490 ↑ 1.0 1 245

Index Scan using academic_session_pkey on academic_session acs (cost=0.14..0.17 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=245)

  • Index Cond: (id = s.school_year_id)
  • Filter: ((('now'::cstring)::date >= start_date) AND (('now'::cstring)::date <= end_date))
31. 0.047 1.282 ↓ 1.0 245 1

Hash (cost=252.56..252.56 rows=243 width=8) (actual time=1.281..1.282 rows=245 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
32. 0.078 1.235 ↓ 1.0 245 1

HashAggregate (cost=247.70..250.13 rows=243 width=8) (actual time=1.206..1.235 rows=245 loops=1)

  • Group Key: students_temp.sid
33. 1.157 1.157 ↑ 1.0 245 1

Seq Scan on students_temp (cost=0.00..247.09 rows=245 width=8) (actual time=0.027..1.157 rows=245 loops=1)

  • Filter: (temps = 'c85d08d8-f454-11ea-95e0-effb0b162435'::uuid)
  • Rows Removed by Filter: 11,042
34.          

SubPlan (for Hash Join)

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on gradingscale_name (cost=0.00..1.07 rows=1 width=7) (never executed)

  • Filter: (id = ($21))
36. 0.000 0.000 ↓ 0.0 0

Seq Scan on gradingscale (cost=0.00..3.99 rows=1 width=4) (never executed)

  • Filter: ((gradingscale_name = ($21)) AND (((CASE WHEN (((sum(CASE WHEN question_is_multiple_choice(q_1.type_id) THEN q_1.weight_total ELSE '0'::double precision END)) + (sum(CASE WHEN (q_1.type_id = ANY ('{3,4}'::integer[])) THEN CASE WHEN q_1.double_scoring THEN (q_1.weight_total * '2'::double precision) ELSE q_1.weight_total END ELSE '0'::double precision END))) > '0'::double precision) THEN ((((sum(CASE WHEN question_is_multiple_choice(q.type_id) THEN r.weighted_score ELSE '0'::double precision END)) + (sum(CASE WHEN question_is_performance_event(q.type_id) THEN CASE WHEN q.double_scoring THEN (r.score * '2'::double precision) ELSE r.score END ELSE '0'::double precision END))) / ((sum(CASE WHEN question_is_multiple_choice(q_1.type_id) THEN q_1.weight_total ELSE '0'::double precision END)) + (sum(CASE WHEN (q_1.type_id = ANY ('{3,4}'::integer[])) THEN CASE WHEN q_1.double_scoring THEN (q_1.weight_total * '2'::double precision) ELSE q_1.weight_total END ELSE '0'::double precision END)))) * '100'::double precision) ELSE NULL::double precision END)::numeric)::double precision >= begin_range) AND (((CASE WHEN (((sum(CASE WHEN question_is_multiple_choice(q_1.type_id) THEN q_1.weight_total ELSE '0'::double precision END)) + (sum(CASE WHEN (q_1.type_id = ANY ('{3,4}'::integer[])) THEN CASE WHEN q_1.double_scoring THEN (q_1.weight_total * '2'::double precision) ELSE q_1.weight_total END ELSE '0'::double precision END))) > '0'::double precision) THEN ((((sum(CASE WHEN question_is_multiple_choice(q.type_id) THEN r.weighted_score ELSE '0'::double precision END)) + (sum(CASE WHEN question_is_performance_event(q.type_id) THEN CASE WHEN q.double_scoring THEN (r.score * '2'::double precision) ELSE r.score END ELSE '0'::double precision END))) / ((sum(CASE WHEN question_is_multiple_choice(q_1.type_id) THEN q_1.weight_total ELSE '0'::double precision END)) + (sum(CASE WHEN (q_1.type_id = ANY ('{3,4}'::integer[])) THEN CASE WHEN q_1.double_scoring THEN (q_1.weight_total * '2'::double precision) ELSE q_1.weight_total END ELSE '0'::double precision END)))) * '100'::double precision) ELSE NULL::double precision END)::numeric)::double precision <= end_range))
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on gradingscale gradingscale_1 (cost=0.00..2.69 rows=1 width=4) (never executed)

  • Filter: ((gradingscale_name = ($21)) AND (((((sum(CASE WHEN question_is_multiple_choice(q.type_id) THEN r.weighted_score ELSE '0'::double precision END)) + (sum(CASE WHEN question_is_performance_event(q.type_id) THEN CASE WHEN q.double_scoring THEN (r.score * '2'::double precision) ELSE r.score END ELSE '0'::double precision END))))::numeric)::double precision >= begin_range) AND (((((sum(CASE WHEN question_is_multiple_choice(q.type_id) THEN r.weighted_score ELSE '0'::double precision END)) + (sum(CASE WHEN question_is_performance_event(q.type_id) THEN CASE WHEN q.double_scoring THEN (r.score * '2'::double precision) ELSE r.score END ELSE '0'::double precision END))))::numeric)::double precision <= end_range))
38. 0.000 0.000 ↓ 0.0 0

Seq Scan on gradingscale_name gradingscale_name_1 (cost=0.00..1.07 rows=1 width=7) (never executed)

  • Filter: (id = ($21))
39. 0.000 0.000 ↓ 0.0 0

Seq Scan on gradingscale gradingscale_2 (cost=0.00..3.21 rows=1 width=4) (never executed)

  • Filter: ((gradingscale_name = ($21)) AND (((CASE WHEN ((sum(CASE WHEN question_is_multiple_choice(q_1.type_id) THEN q_1.weight_total ELSE '0'::double precision END)) > '0'::double precision) THEN (((sum(CASE WHEN question_is_multiple_choice(q.type_id) THEN r.weighted_score ELSE '0'::double precision END)) / (sum(CASE WHEN question_is_multiple_choice(q_1.type_id) THEN q_1.weight_total ELSE '0'::double precision END))) * '100'::double precision) ELSE NULL::double precision END)::numeric)::double precision >= begin_range) AND (((CASE WHEN ((sum(CASE WHEN question_is_multiple_choice(q_1.type_id) THEN q_1.weight_total ELSE '0'::double precision END)) > '0'::double precision) THEN (((sum(CASE WHEN question_is_multiple_choice(q.type_id) THEN r.weighted_score ELSE '0'::double precision END)) / (sum(CASE WHEN question_is_multiple_choice(q_1.type_id) THEN q_1.weight_total ELSE '0'::double precision END))) * '100'::double precision) ELSE NULL::double precision END)::numeric)::double precision <= end_range))
40. 0.000 0.000 ↓ 0.0 0

Seq Scan on gradingscale gradingscale_3 (cost=0.00..2.43 rows=1 width=4) (never executed)

  • Filter: ((gradingscale_name = ($21)) AND ((((sum(CASE WHEN question_is_multiple_choice(q.type_id) THEN r.weighted_score ELSE '0'::double precision END)))::numeric)::double precision >= begin_range) AND ((((sum(CASE WHEN question_is_multiple_choice(q.type_id) THEN r.weighted_score ELSE '0'::double precision END)))::numeric)::double precision <= end_range))
41. 0.000 0.000 ↓ 0.0 0

Seq Scan on gradingscale_name gradingscale_name_2 (cost=0.00..1.07 rows=1 width=7) (never executed)

  • Filter: (id = ($21))
42. 0.000 0.000 ↓ 0.0 0

Seq Scan on gradingscale gradingscale_4 (cost=0.00..3.21 rows=1 width=4) (never executed)

  • Filter: ((gradingscale_name = ($21)) AND (((CASE WHEN ((sum(CASE WHEN (q_1.type_id = ANY ('{3,4}'::integer[])) THEN CASE WHEN q_1.double_scoring THEN (q_1.weight_total * '2'::double precision) ELSE q_1.weight_total END ELSE '0'::double precision END)) > '0'::double precision) THEN (((sum(CASE WHEN question_is_performance_event(q.type_id) THEN CASE WHEN q.double_scoring THEN (r.score * '2'::double precision) ELSE r.score END ELSE '0'::double precision END)) / (sum(CASE WHEN (q_1.type_id = ANY ('{3,4}'::integer[])) THEN CASE WHEN q_1.double_scoring THEN (q_1.weight_total * '2'::double precision) ELSE q_1.weight_total END ELSE '0'::double precision END))) * '100'::double precision) ELSE NULL::double precision END)::numeric)::double precision >= begin_range) AND (((CASE WHEN ((sum(CASE WHEN (q_1.type_id = ANY ('{3,4}'::integer[])) THEN CASE WHEN q_1.double_scoring THEN (q_1.weight_total * '2'::double precision) ELSE q_1.weight_total END ELSE '0'::double precision END)) > '0'::double precision) THEN (((sum(CASE WHEN question_is_performance_event(q.type_id) THEN CASE WHEN q.double_scoring THEN (r.score * '2'::double precision) ELSE r.score END ELSE '0'::double precision END)) / (sum(CASE WHEN (q_1.type_id = ANY ('{3,4}'::integer[])) THEN CASE WHEN q_1.double_scoring THEN (q_1.weight_total * '2'::double precision) ELSE q_1.weight_total END ELSE '0'::double precision END))) * '100'::double precision) ELSE NULL::double precision END)::numeric)::double precision <= end_range))
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on gradingscale gradingscale_5 (cost=0.00..2.43 rows=1 width=4) (never executed)

  • Filter: ((gradingscale_name = ($21)) AND ((((sum(CASE WHEN question_is_performance_event(q.type_id) THEN CASE WHEN q.double_scoring THEN (r.score * '2'::double precision) ELSE r.score END ELSE '0'::double precision END)))::numeric)::double precision >= begin_range) AND ((((sum(CASE WHEN question_is_performance_event(q.type_id) THEN CASE WHEN q.double_scoring THEN (r.score * '2'::double precision) ELSE r.score END ELSE '0'::double precision END)))::numeric)::double precision <= end_range))
Planning time : 4.854 ms
Execution time : 129.447 ms