explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o5L5

Settings
# exclusive inclusive rows x rows loops node
1. 0.495 42,948.816 ↓ 122.5 245 1

Sort (cost=3,066.04..3,066.05 rows=2 width=217) (actual time=42,948.803..42,948.816 rows=245 loops=1)

  • Sort Key: s.last_name, s.first_name
  • Sort Method: quicksort Memory: 90kB
2. 0.296 42,948.321 ↓ 122.5 245 1

Hash Join (cost=2,901.87..3,066.03 rows=2 width=217) (actual time=42,877.596..42,948.321 rows=245 loops=1)

  • Hash Cond: ((tr.sid)::text = (students_temp.sid)::text)
3. 0.008 42,947.740 ↓ 122.5 245 1

Nested Loop Left Join (cost=2,863.50..2,985.20 rows=2 width=97) (actual time=42,877.287..42,947.740 rows=245 loops=1)

4. 0.136 42,944.547 ↓ 245.0 245 1

Merge Join (cost=2,858.88..2,952.16 rows=1 width=81) (actual time=42,877.239..42,944.547 rows=245 loops=1)

  • Merge Cond: (tr.id = tr_1.id)
5. 38.616 21,847.186 ↓ 245.0 245 1

GroupAggregate (cost=2,040.53..2,041.31 rows=1 width=103) (actual time=21,807.866..21,847.186 rows=245 loops=1)

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

Initplan (for GroupAggregate)

7. 0.019 0.019 ↑ 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.018..0.019 rows=1 loops=1)

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

Sort (cost=2,032.09..2,032.10 rows=1 width=106) (actual time=21,807.467..21,808.551 rows=9,310 loops=1)

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

Nested Loop (cost=15.71..2,032.08 rows=1 width=106) (actual time=0.198..21,791.645 rows=9,310 loops=1)

10. 10.745 21,674.151 ↓ 63.4 9,766 1

Nested Loop (cost=15.02..689.97 rows=154 width=98) (actual time=0.176..21,674.151 rows=9,766 loops=1)

11. 0.051 0.556 ↓ 38.0 38 1

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

12. 0.003 0.045 ↑ 1.0 1 1

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

13. 0.025 0.025 ↑ 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.024..0.025 rows=1 loops=1)

  • Index Cond: (id = '22add6e2-e8ab-11ea-87e4-837e6f2587b0'::uuid)
  • Heap Fetches: 1
14. 0.017 0.017 ↑ 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.017..0.017 rows=1 loops=1)

  • Index Cond: (id = ta.test_id)
15. 0.460 0.460 ↓ 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.044..0.460 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. 21,661.102 21,662.850 ↓ 1.7 257 38

Bitmap Heap Scan on test_registration tr (cost=13.75..621.22 rows=154 width=79) (actual time=0.181..570.075 rows=257 loops=38)

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

Bitmap Index Scan on test_registration_test_administration_id (cost=0.00..13.71 rows=154 width=0) (actual time=0.046..0.046 rows=456 loops=38)

  • Index Cond: (test_administration_id = '22add6e2-e8ab-11ea-87e4-837e6f2587b0'::uuid)
18. 87.894 87.894 ↑ 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.009..0.009 rows=1 loops=9,766)

  • Index Cond: ((question_id = q.id) AND (test_registration_id = tr.id))
19. 26.879 21,097.225 ↓ 1.7 257 1

GroupAggregate (cost=818.36..908.91 rows=154 width=40) (actual time=21,069.368..21,097.225 rows=257 loops=1)

  • Group Key: tr_1.id
20. 13.713 21,070.346 ↓ 56.8 9,766 1

Sort (cost=818.36..818.79 rows=172 width=27) (actual time=21,069.168..21,070.346 rows=9,766 loops=1)

  • Sort Key: tr_1.id
  • Sort Method: quicksort Memory: 1,147kB
21. 7.120 21,056.633 ↓ 56.8 9,766 1

Nested Loop (cost=14.60..811.97 rows=172 width=27) (actual time=0.132..21,056.633 rows=9,766 loops=1)

22. 0.058 0.667 ↓ 38.0 38 1

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

23. 0.022 0.022 ↑ 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.020..0.022 rows=1 loops=1)

  • Index Cond: (id = '22add6e2-e8ab-11ea-87e4-837e6f2587b0'::uuid)
  • Heap Fetches: 1
24. 0.587 0.587 ↓ 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.024..0.587 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. 21,047.136 21,048.846 ↓ 1.7 257 38

Bitmap Heap Scan on test_registration tr_1 (cost=13.75..621.22 rows=154 width=32) (actual time=0.175..553.917 rows=257 loops=38)

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

Bitmap Index Scan on test_registration_test_administration_id (cost=0.00..13.71 rows=154 width=0) (actual time=0.045..0.045 rows=456 loops=38)

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

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

28. 0.490 2.205 ↑ 7.0 1 245

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

  • Recheck Cond: ((sid)::text = (tr.sid)::text)
  • Heap Blocks: exact=245
29. 1.715 1.715 ↑ 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.007..0.007 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.038 0.285 ↓ 1.5 245 1

Hash (cost=36.32..36.32 rows=164 width=8) (actual time=0.285..0.285 rows=245 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
32. 0.090 0.247 ↓ 1.5 245 1

HashAggregate (cost=33.04..34.68 rows=164 width=8) (actual time=0.222..0.247 rows=245 loops=1)

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

Seq Scan on students_temp (cost=0.00..32.42 rows=245 width=8) (actual time=0.039..0.157 rows=245 loops=1)

  • Filter: (temps = 'c85d08d8-f454-11ea-95e0-effb0b162435'::uuid)
  • Rows Removed by Filter: 1,229
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 : 9.493 ms
Execution time : 42,949.754 ms