explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tiOC : Optimization for: plan #9fIf

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 565.205 503,528.985 ↓ 6.5 1,306 1

GroupAggregate (cost=870.66..2,060,539.11 rows=200 width=648) (actual time=5,000.595..503,528.985 rows=1,306 loops=1)

  • Group Key: ac.school_k12id, ac.school_name, ac.school_id
2.          

CTE accounts

3. 198.263 3,721.736 ↓ 345.9 469,371 1

Hash Join (cost=644.13..772.92 rows=1,357 width=444) (actual time=3,331.156..3,721.736 rows=469,371 loops=1)

  • Hash Cond: (smc.id = smst.sm_classid)
4. 194.484 194.484 ↓ 31.6 69,917 1

Foreign Scan on sm_class smc (cost=100.00..176.42 rows=2,214 width=13) (actual time=1.998..194.484 rows=69,917 loops=1)

5. 146.211 3,328.989 ↓ 3,816.0 469,371 1

Hash (cost=542.59..542.59 rows=123 width=439) (actual time=3,328.989..3,328.989 rows=469,371 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 16 (originally 1) Memory Usage: 3585kB
6. 348.265 3,182.778 ↓ 3,816.0 469,371 1

Hash Join (cost=456.65..542.59 rows=123 width=439) (actual time=1,147.031..3,182.778 rows=469,371 loops=1)

  • Hash Cond: (smst.sm_masterstudentid = smms.id)
7. 1,688.679 1,688.679 ↓ 310.4 687,332 1

Foreign Scan on sm_student smst (cost=100.00..176.42 rows=2,214 width=13) (actual time=1.111..1,688.679 rows=687,332 loops=1)

8. 96.561 1,145.834 ↓ 25,921.9 285,141 1

Hash (cost=356.51..356.51 rows=11 width=434) (actual time=1,145.834..1,145.834 rows=285,141 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3585kB
9. 115.534 1,049.273 ↓ 25,921.9 285,141 1

Hash Join (cost=271.68..356.51 rows=11 width=434) (actual time=22.256..1,049.273 rows=285,141 loops=1)

  • Hash Cond: (smms.sm_schoolid = smsc.id)
10. 912.691 912.691 ↓ 166.6 368,917 1

Foreign Scan on sm_masterstudent smms (cost=100.00..176.42 rows=2,214 width=13) (actual time=1.194..912.691 rows=368,917 loops=1)

11. 0.640 21.048 ↓ 1,430.0 1,430 1

Hash (cost=171.67..171.67 rows=1 width=429) (actual time=21.048..21.048 rows=1,430 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 124kB
12. 20.408 20.408 ↓ 1,430.0 1,430 1

Foreign Scan on sm_school smsc (cost=100.00..171.67 rows=1 width=429) (actual time=3.401..20.408 rows=1,430 loops=1)

13. 794.087 4,773.373 ↓ 345.9 469,371 1

Sort (cost=97.75..101.14 rows=1,357 width=435) (actual time=4,576.532..4,773.373 rows=469,371 loops=1)

  • Sort Key: ac.school_k12id, ac.school_name, ac.school_id
  • Sort Method: external merge Disk: 26368kB
14. 3,979.286 3,979.286 ↓ 345.9 469,371 1

CTE Scan on accounts ac (cost=0.00..27.14 rows=1,357 width=435) (actual time=3,331.165..3,979.286 rows=469,371 loops=1)

15.          

SubPlan (forGroupAggregate)

16. 3.918 98,838.080 ↑ 1.0 1 1,306

Aggregate (cost=97.70..97.74 rows=1 width=32) (actual time=75.680..75.680 rows=1 loops=1,306)

17. 0.000 98,834.162 ↑ 1.8 5 1,306

Nested Loop (cost=30.98..97.61 rows=9 width=8) (actual time=75.625..75.677 rows=5 loops=1,306)

18. 178.922 98,030.972 ↓ 52.3 366 1,306

HashAggregate (cost=30.55..30.62 rows=7 width=4) (actual time=75.015..75.062 rows=366 loops=1,306)

  • Group Key: a.student_id
19. 97,852.050 97,852.050 ↓ 52.3 366 1,306

CTE Scan on accounts a (cost=0.00..30.53 rows=7 width=4) (actual time=11.103..74.925 rows=366 loops=1,306)

  • Filter: (school_id = ac.school_id)
  • Rows Removed by Filter: 469005
20. 956.168 956.168 ↓ 0.0 0 478,084

Index Scan using uniqueconstraint on dailystudentanswers d (cost=0.43..9.56 rows=1 width=12) (actual time=0.001..0.002 rows=0 loops=478,084)

  • Index Cond: ((studentid = a.student_id) AND (testtypeid = 4))
21. 10.448 98,889.014 ↑ 1.0 1 1,306

Aggregate (cost=428.20..428.24 rows=1 width=32) (actual time=75.719..75.719 rows=1 loops=1,306)

22. 0.000 98,878.566 ↑ 2.7 33 1,306

Nested Loop (cost=30.98..427.31 rows=89 width=8) (actual time=75.469..75.711 rows=33 loops=1,306)

23. 177.616 97,981.344 ↓ 52.3 366 1,306

HashAggregate (cost=30.55..30.62 rows=7 width=4) (actual time=74.978..75.024 rows=366 loops=1,306)

  • Group Key: a_1.student_id
24. 97,803.728 97,803.728 ↓ 52.3 366 1,306

CTE Scan on accounts a_1 (cost=0.00..30.53 rows=7 width=4) (actual time=11.079..74.888 rows=366 loops=1,306)

  • Filter: (school_id = ac.school_id)
  • Rows Removed by Filter: 469005
25. 956.168 956.168 ↓ 0.0 0 478,084

Index Scan using uniqueconstraint on dailystudentanswers d_1 (cost=0.43..56.54 rows=13 width=12) (actual time=0.001..0.002 rows=0 loops=478,084)

  • Index Cond: ((studentid = a_1.student_id) AND (testtypeid = 5))
26. 28.732 101,076.564 ↑ 1.0 1 1,306

Aggregate (cost=3,073.13..3,073.18 rows=1 width=32) (actual time=77.394..77.394 rows=1 loops=1,306)

27. 147.050 101,047.832 ↑ 2.0 131 1,306

Nested Loop (cost=30.98..3,070.52 rows=261 width=8) (actual time=75.443..77.372 rows=131 loops=1,306)

28. 181.534 98,032.278 ↓ 52.3 366 1,306

HashAggregate (cost=30.55..30.62 rows=7 width=4) (actual time=75.014..75.063 rows=366 loops=1,306)

  • Group Key: a_2.student_id
29. 97,850.744 97,850.744 ↓ 52.3 366 1,306

CTE Scan on accounts a_2 (cost=0.00..30.53 rows=7 width=4) (actual time=11.081..74.924 rows=366 loops=1,306)

  • Filter: (school_id = ac.school_id)
  • Rows Removed by Filter: 469005
30. 2,868.504 2,868.504 ↓ 0.0 0 478,084

Index Scan using dailystudentanswers_studentid_idx on dailystudentanswers d_2 (cost=0.43..433.90 rows=37 width=12) (actual time=0.003..0.006 rows=0 loops=478,084)

  • Index Cond: (studentid = a_2.student_id)
  • Filter: (topicgradeid < studentgradeid)
  • Rows Removed by Filter: 1
31. 40.486 99,804.520 ↑ 1.0 1 1,306

Aggregate (cost=3,068.04..3,068.09 rows=1 width=32) (actual time=76.420..76.420 rows=1 loops=1,306)

32. 204.778 99,764.034 ↓ 70.2 281 1,306

Nested Loop (cost=30.98..3,068.00 rows=4 width=8) (actual time=75.461..76.389 rows=281 loops=1,306)

33. 181.534 98,125.004 ↓ 52.3 366 1,306

HashAggregate (cost=30.55..30.62 rows=7 width=4) (actual time=75.087..75.134 rows=366 loops=1,306)

  • Group Key: a_3.student_id
34. 97,943.470 97,943.470 ↓ 52.3 366 1,306

CTE Scan on accounts a_3 (cost=0.00..30.53 rows=7 width=4) (actual time=11.086..74.995 rows=366 loops=1,306)

  • Filter: (school_id = ac.school_id)
  • Rows Removed by Filter: 469005
35. 1,434.252 1,434.252 ↑ 1.0 1 478,084

Index Scan using dailystudentanswers_studentid_idx on dailystudentanswers d_3 (cost=0.43..433.90 rows=1 width=12) (actual time=0.001..0.003 rows=1 loops=478,084)

  • Index Cond: (studentid = a_3.student_id)
  • Filter: (topicgradeid = studentgradeid)
  • Rows Removed by Filter: 0
36. 6.530 99,548.544 ↑ 1.0 1 1,306

Aggregate (cost=3,073.13..3,073.18 rows=1 width=32) (actual time=76.224..76.224 rows=1 loops=1,306)

37. 15.408 99,542.014 ↑ 18.6 14 1,306

Nested Loop (cost=30.98..3,070.52 rows=261 width=8) (actual time=75.555..76.219 rows=14 loops=1,306)

38. 181.534 98,092.354 ↓ 52.3 366 1,306

HashAggregate (cost=30.55..30.62 rows=7 width=4) (actual time=75.062..75.109 rows=366 loops=1,306)

  • Group Key: a_4.student_id
39. 97,910.820 97,910.820 ↓ 52.3 366 1,306

CTE Scan on accounts a_4 (cost=0.00..30.53 rows=7 width=4) (actual time=11.086..74.970 rows=366 loops=1,306)

  • Filter: (school_id = ac.school_id)
  • Rows Removed by Filter: 469005
40. 1,434.252 1,434.252 ↓ 0.0 0 478,084

Index Scan using dailystudentanswers_studentid_idx on dailystudentanswers d_4 (cost=0.43..433.90 rows=37 width=12) (actual time=0.002..0.003 rows=0 loops=478,084)

  • Index Cond: (studentid = a_4.student_id)
  • Filter: (topicgradeid > studentgradeid)
  • Rows Removed by Filter: 1
41. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on classsubject cs_10 (cost=9.54..13.56 rows=1 width=0) (never executed)

  • Recheck Cond: ((classid = ac.class_id) AND (subjectid = 10))
42. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=9.54..9.54 rows=1 width=0) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on classsubject_classid_idx (cost=0.00..4.44 rows=3 width=0) (never executed)

  • Index Cond: (classid = ac.class_id)
44. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on classsubject_subjectid_idx (cost=0.00..4.85 rows=58 width=0) (never executed)

  • Index Cond: (subjectid = 10)
45. 0.456 0.456 ↓ 1.2 72 1

Index Scan using classsubject_subjectid_idx on classsubject cs_11 (cost=0.42..106.52 rows=58 width=4) (actual time=0.071..0.456 rows=72 loops=1)

  • Index Cond: (subjectid = 10)
46. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on classsubject cs_8 (cost=9.54..13.56 rows=1 width=0) (never executed)

  • Recheck Cond: ((classid = ac.class_id) AND (subjectid = 9))
47. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=9.54..9.54 rows=1 width=0) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on classsubject_classid_idx (cost=0.00..4.44 rows=3 width=0) (never executed)

  • Index Cond: (classid = ac.class_id)
49. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on classsubject_subjectid_idx (cost=0.00..4.85 rows=58 width=0) (never executed)

  • Index Cond: (subjectid = 9)
50. 0.033 0.033 ↓ 1.3 75 1

Index Scan using classsubject_subjectid_idx on classsubject cs_9 (cost=0.42..106.52 rows=58 width=4) (actual time=0.013..0.033 rows=75 loops=1)

  • Index Cond: (subjectid = 9)
51. 0.000 0.000 ↓ 0.0 0

Index Scan using classsubject_classid_idx on classsubject cs_6 (cost=0.42..13.77 rows=1 width=0) (never executed)

  • Index Cond: (classid = ac.class_id)
  • Filter: (subjectid = 11)
52. 14.200 14.200 ↓ 1.1 28,544 1

Seq Scan on classsubject cs_7 (cost=0.00..2,069.23 rows=25,629 width=4) (actual time=6.006..14.200 rows=28,544 loops=1)

  • Filter: (subjectid = 11)
  • Rows Removed by Filter: 63486
53. 0.000 0.000 ↓ 0.0 0

Index Scan using classsubject_classid_idx on classsubject cs_4 (cost=0.42..13.77 rows=1 width=0) (never executed)

  • Index Cond: (classid = ac.class_id)
  • Filter: (subjectid = 8)
54. 1.156 2.021 ↓ 1.1 6,194 1

Bitmap Heap Scan on classsubject cs_5 (cost=372.43..1,478.42 rows=5,679 width=4) (actual time=0.908..2.021 rows=6,194 loops=1)

  • Recheck Cond: (subjectid = 8)
  • Heap Blocks: exact=402
55. 0.865 0.865 ↓ 1.2 6,582 1

Bitmap Index Scan on classsubject_subjectid_idx (cost=0.00..371.01 rows=5,679 width=0) (actual time=0.865..0.865 rows=6,582 loops=1)

  • Index Cond: (subjectid = 8)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using classsubject_classid_idx on classsubject cs_2 (cost=0.42..13.77 rows=1 width=0) (never executed)

  • Index Cond: (classid = ac.class_id)
  • Filter: (subjectid = 2)
57. 8.525 8.525 ↓ 1.1 28,588 1

Seq Scan on classsubject cs_3 (cost=0.00..2,069.23 rows=25,811 width=4) (actual time=0.005..8.525 rows=28,588 loops=1)

  • Filter: (subjectid = 2)
  • Rows Removed by Filter: 63442
58. 0.000 0.000 ↓ 0.0 0

Index Scan using classsubject_classid_idx on classsubject cs (cost=0.42..13.77 rows=1 width=0) (never executed)

  • Index Cond: (classid = ac.class_id)
  • Filter: (subjectid = 1)
59. 8.450 8.450 ↓ 1.1 28,557 1

Seq Scan on classsubject cs_1 (cost=0.00..2,069.23 rows=25,503 width=4) (actual time=0.005..8.450 rows=28,557 loops=1)

  • Filter: (subjectid = 1)
  • Rows Removed by Filter: 63473