explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ior

Settings
# exclusive inclusive rows x rows loops node
1. 0.385 196,780.403 ↓ 23.0 23 1

Unique (cost=458,113.38..458,113.42 rows=1 width=292) (actual time=196,779.736..196,780.403 rows=23 loops=1)

2.          

CTE courses_assigned_to_test

3. 0.392 60.992 ↓ 11.5 23 1

Unique (cost=2,499.89..2,499.93 rows=2 width=192) (actual time=60.420..60.992 rows=23 loops=1)

4. 0.384 60.600 ↓ 11.5 23 1

Sort (cost=2,499.89..2,499.89 rows=2 width=192) (actual time=60.405..60.600 rows=23 loops=1)

  • Sort Key: courses_full.id, courses_full.course_name, courses_full.course_number, courses_full.section, s_1.teacher_name, s_1.teacher_number, t.test_administration_id
  • Sort Method: quicksort Memory: 28kB
5. 0.351 60.216 ↓ 11.5 23 1

Append (cost=1,806.99..2,499.88 rows=2 width=192) (actual time=32.520..60.216 rows=23 loops=1)

6. 4.143 40.564 ↓ 23.0 23 1

Unique (cost=1,806.99..1,807.01 rows=1 width=81) (actual time=32.504..40.564 rows=23 loops=1)

7. 8.104 36.421 ↓ 495.0 495 1

Sort (cost=1,806.99..1,806.99 rows=1 width=81) (actual time=32.490..36.421 rows=495 loops=1)

  • Sort Key: courses_full.id, courses_full.course_name, courses_full.course_number, courses_full.section, s_1.teacher_name, s_1.teacher_number
  • Sort Method: quicksort Memory: 94kB
8. 7.741 28.317 ↓ 495.0 495 1

Nested Loop (cost=0.71..1,806.98 rows=1 width=81) (actual time=13.340..28.317 rows=495 loops=1)

9. 0.796 15.426 ↓ 2.8 25 1

Nested Loop (cost=0.29..1,796.74 rows=9 width=66) (actual time=13.251..15.426 rows=25 loops=1)

10. 14.080 14.080 ↓ 2.8 25 1

Seq Scan on test_administration_course_section t (cost=0.00..1,721.89 rows=9 width=32) (actual time=13.206..14.080 rows=25 loops=1)

  • Filter: (test_administration_id = '8dce6f88-abc0-11e8-b73b-c4a391ab38c2'::uuid)
  • Rows Removed by Filter: 78766
11. 0.550 0.550 ↑ 1.0 1 25

Index Scan using courses_pkey on courses_full (cost=0.29..8.31 rows=1 width=50) (actual time=0.014..0.022 rows=1 loops=25)

  • Index Cond: (id = t.courseid)
12. 5.150 5.150 ↓ 20.0 20 25

Index Scan using schedule_teacher_section on schedule s_1 (cost=0.42..1.13 rows=1 width=49) (actual time=0.020..0.206 rows=20 loops=25)

  • Index Cond: ((course_number = courses_full.course_number) AND (section = courses_full.section) AND (teacher_number = courses_full.teacher_number))
  • Filter: (courses_full.course_name = course_name)
13. 0.016 19.301 ↓ 0.0 0 1

Unique (cost=692.83..692.85 rows=1 width=81) (actual time=19.292..19.301 rows=0 loops=1)

14. 0.033 19.285 ↓ 0.0 0 1

Sort (cost=692.83..692.84 rows=1 width=81) (actual time=19.277..19.285 rows=0 loops=1)

  • Sort Key: courses_full_1.id, courses_full_1.course_name, courses_full_1.course_number, courses_full_1.section, s_2.teacher_name, s_2.teacher_number
  • Sort Method: quicksort Memory: 25kB
15. 0.014 19.252 ↓ 0.0 0 1

Nested Loop (cost=64.56..692.82 rows=1 width=81) (actual time=19.244..19.252 rows=0 loops=1)

  • Join Filter: (uniquecourses_full.course_name = s_2.course_name)
16. 0.022 19.238 ↓ 0.0 0 1

Nested Loop (cost=64.14..646.10 rows=41 width=86) (actual time=19.229..19.238 rows=0 loops=1)

17. 0.010 19.216 ↓ 0.0 0 1

Merge Join (cost=63.85..641.67 rows=1 width=36) (actual time=19.207..19.216 rows=0 loops=1)

  • Merge Cond: (t_1.uniquecourse = uniquecourses_full.id)
18. 19.206 19.206 ↑ 9.0 1 1

Index Scan using test_administration_course_section_uniquecourse on test_administration_course_section t_1 (cost=0.29..5,159.12 rows=9 width=32) (actual time=19.190..19.206 rows=1 loops=1)

  • Filter: (test_administration_id = '8dce6f88-abc0-11e8-b73b-c4a391ab38c2'::uuid)
  • Rows Removed by Filter: 74403
19. 0.000 0.000 ↓ 0.0 0

Sort (cost=63.56..65.86 rows=921 width=36) (never executed)

  • Sort Key: uniquecourses_full.id
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on uniquecourses_full (cost=0.00..18.21 rows=921 width=36) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using courses_course_name_ix on courses_full courses_full_1 (cost=0.29..4.01 rows=42 width=50) (never executed)

  • Index Cond: (course_name = uniquecourses_full.course_name)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using schedule_teacher_section on schedule s_2 (cost=0.42..1.13 rows=1 width=49) (never executed)

  • Index Cond: ((course_number = courses_full_1.course_number) AND (section = courses_full_1.section) AND (teacher_number = courses_full_1.teacher_number))
  • Filter: (courses_full_1.course_name = course_name)
23. 0.550 196,780.018 ↓ 23.0 23 1

Sort (cost=455,613.45..455,613.46 rows=1 width=292) (actual time=196,779.720..196,780.018 rows=23 loops=1)

  • Sort Key: c.teacher_name, c.teacher_number, ((((c.teacher_name || ' ('::text) || c.teacher_number) || ')'::text)), c.course_name, c.course_number, c.section, (COALESCE((count(*)), '0'::bigint)), (count(*)), s2.building, (min(tr2.created_date)), (max(tr2.created_date)), (to_char((min(tr2.created_date)), 'Mon DD YYYY HH:MI AM'::text)), (to_char((max(tr2.created_date)), 'Mon DD YYYY HH:MI AM'::text))
  • Sort Method: quicksort Memory: 30kB
24. 140.891 196,779.468 ↓ 23.0 23 1

Merge Right Join (cost=454,674.39..455,613.44 rows=1 width=292) (actual time=196,769.637..196,779.468 rows=23 loops=1)

  • Merge Cond: ((tr2.test_administration_id = ta.id) AND (trct.teacher_number = c.teacher_number) AND (s.course_name = c.course_name) AND (s.course_number = c.course_number) AND (s.section = c.section))
25. 4,217.328 190,831.874 ↓ 1.0 17,762 1

GroupAggregate (cost=440,319.39..440,874.27 rows=17,073 width=74) (actual time=182,068.627..190,831.874 rows=17,762 loops=1)

  • Group Key: tr2.test_administration_id, trct.teacher_number, s.course_name, s.course_number, s.section
26. 19,326.340 186,614.546 ↓ 28.5 487,052 1

Sort (cost=440,319.39..440,362.08 rows=17,073 width=58) (actual time=182,067.587..186,614.546 rows=487,052 loops=1)

  • Sort Key: tr2.test_administration_id, trct.teacher_number, s.course_name, s.course_number, s.section
  • Sort Method: external merge Disk: 66544kB
27. 28,182.969 167,288.206 ↓ 51.4 877,942 1

Merge Join (cost=424,757.84..439,119.21 rows=17,073 width=58) (actual time=109,535.741..167,288.206 rows=877,942 loops=1)

  • Merge Cond: (((tr2.sid)::text = (s.sid)::text) AND (trct.teacher_number = s.teacher_number))
28. 40,950.603 117,698.815 ↑ 1.0 1,464,053 1

Sort (cost=376,444.30..380,104.77 rows=1,464,188 width=36) (actual time=102,276.017..117,698.815 rows=1,464,053 loops=1)

  • Sort Key: tr2.sid, trct.teacher_number
  • Sort Method: external merge Disk: 67112kB
29. 31,899.347 76,748.212 ↑ 1.0 1,464,188 1

Hash Join (cost=69,260.76..146,426.23 rows=1,464,188 width=36) (actual time=31,506.681..76,748.212 rows=1,464,188 loops=1)

  • Hash Cond: (trct.test_registration_id = tr2.id)
30. 13,347.733 13,347.733 ↑ 1.0 1,464,188 1

Seq Scan on test_registration_course_teacher trct (cost=0.00..26,843.88 rows=1,464,188 width=22) (actual time=1.045..13,347.733 rows=1,464,188 loops=1)

31. 18,444.339 31,501.132 ↑ 1.0 1,482,345 1

Hash (cost=37,702.45..37,702.45 rows=1,482,345 width=46) (actual time=31,501.125..31,501.132 rows=1,482,345 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2292kB
32. 13,056.793 13,056.793 ↑ 1.0 1,482,345 1

Seq Scan on test_registration tr2 (cost=0.00..37,702.45 rows=1,482,345 width=46) (actual time=0.599..13,056.793 rows=1,482,345 loops=1)

33. 10,667.229 21,406.422 ↓ 2.9 986,297 1

Materialize (cost=48,259.90..49,944.38 rows=336,897 width=39) (actual time=7,230.541..21,406.422 rows=986,297 loops=1)

34. 7,984.182 10,739.193 ↑ 1.0 336,897 1

Sort (cost=48,259.90..49,102.14 rows=336,897 width=39) (actual time=7,230.522..10,739.193 rows=336,897 loops=1)

  • Sort Key: s.sid, s.teacher_number
  • Sort Method: external merge Disk: 17424kB
35. 2,755.011 2,755.011 ↑ 1.0 336,897 1

Seq Scan on schedule s (cost=0.00..8,113.97 rows=336,897 width=39) (actual time=2.128..2,755.011 rows=336,897 loops=1)

36. 0.460 5,806.703 ↓ 23.0 23 1

Sort (cost=14,354.99..14,355.00 rows=1 width=188) (actual time=5,806.517..5,806.703 rows=23 loops=1)

  • Sort Key: ta.id, c.teacher_number, c.course_name, c.course_number, c.section
  • Sort Method: quicksort Memory: 28kB
37. 0.762 5,806.243 ↓ 23.0 23 1

Nested Loop (cost=13,167.80..14,354.98 rows=1 width=188) (actual time=5,678.464..5,806.243 rows=23 loops=1)

38. 66.665 5,804.837 ↓ 23.0 23 1

Hash Join (cost=13,167.51..14,346.67 rows=1 width=188) (actual time=5,678.388..5,804.837 rows=23 loops=1)

  • Hash Cond: ((s2.course_name = c.course_name) AND (s2.teacher_number = c.teacher_number) AND (s2.course_number = c.course_number) AND (s2.section = c.section))
39. 3,050.881 5,676.561 ↑ 4.0 8,473 1

HashAggregate (cost=13,167.42..13,504.32 rows=33,690 width=46) (actual time=5,607.721..5,676.561 rows=8,473 loops=1)

  • Group Key: s2.course_name, s2.teacher_number, s2.course_number, s2.section, s2.building
40. 2,625.680 2,625.680 ↑ 1.0 336,897 1

Seq Scan on schedule s2 (cost=0.00..8,113.97 rows=336,897 width=38) (actual time=1.040..2,625.680 rows=336,897 loops=1)

41. 0.234 61.611 ↓ 11.5 23 1

Hash (cost=0.04..0.04 rows=2 width=176) (actual time=61.604..61.611 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
42. 61.377 61.377 ↓ 11.5 23 1

CTE Scan on courses_assigned_to_test c (cost=0.00..0.04 rows=2 width=176) (actual time=60.437..61.377 rows=23 loops=1)

  • Filter: ((teacher_number IS NOT NULL) OR (teacher_number <> ''::text))
43. 0.644 0.644 ↑ 1.0 1 23

Index Only Scan using test_administration_test_id on test_administration ta (cost=0.29..8.30 rows=1 width=16) (actual time=0.020..0.028 rows=1 loops=23)

  • Index Cond: (id = c.test_administration_id)
  • Heap Fetches: 23