explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9fIf

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 618.964 540,744.041 ↓ 6.5 1,306 1

GroupAggregate (cost=921.55..2,060,590.00 rows=200 width=648) (actual time=5,622.684..540,744.041 rows=1,306 loops=1)

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

CTE accounts

3. 433.123 4,067.784 ↓ 345.9 469,333 1

HashAggregate (cost=810.23..823.80 rows=1,357 width=444) (actual time=3,899.114..4,067.784 rows=469,333 loops=1)

  • Group Key: smst.dsid, smst.active, smms.dsid, smms.active, smc.dsid, smc.active, smc.gradeid, smsc.k12id, smsc.dsid, smsc.active, smsc.name
4. 198.754 3,634.661 ↓ 345.9 469,333 1

Hash Join (cost=644.13..772.92 rows=1,357 width=444) (actual time=3,257.842..3,634.661 rows=469,333 loops=1)

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

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

6. 146.734 3,255.922 ↓ 3,815.7 469,333 1

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

  • Buckets: 65536 (originally 1024) Batches: 16 (originally 1) Memory Usage: 3585kB
7. 350.819 3,109.188 ↓ 3,815.7 469,333 1

Hash Join (cost=456.65..542.59 rows=123 width=439) (actual time=1,116.077..3,109.188 rows=469,333 loops=1)

  • Hash Cond: (smst.sm_masterstudentid = smms.id)
8. 1,643.599 1,643.599 ↓ 310.4 687,287 1

Foreign Scan on sm_student smst (cost=100.00..176.42 rows=2,214 width=13) (actual time=1.144..1,643.599 rows=687,287 loops=1)

9. 94.333 1,114.770 ↓ 25,919.7 285,117 1

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

  • Buckets: 65536 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3585kB
10. 110.497 1,020.437 ↓ 25,919.7 285,117 1

Hash Join (cost=271.68..356.51 rows=11 width=434) (actual time=21.237..1,020.437 rows=285,117 loops=1)

  • Hash Cond: (smms.sm_schoolid = smsc.id)
11. 889.798 889.798 ↓ 166.6 368,879 1

Foreign Scan on sm_masterstudent smms (cost=100.00..176.42 rows=2,214 width=13) (actual time=1.080..889.798 rows=368,879 loops=1)

12. 0.616 20.142 ↓ 1,430.0 1,430 1

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

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

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

14. 1,036.135 5,379.952 ↓ 345.9 469,333 1

Sort (cost=97.75..101.14 rows=1,357 width=435) (actual time=5,172.081..5,379.952 rows=469,333 loops=1)

  • Sort Key: ac.school_k12id, ac.school_name, ac.school_id
  • Sort Method: external merge Disk: 26368kB
15. 4,343.817 4,343.817 ↓ 345.9 469,333 1

CTE Scan on accounts ac (cost=0.00..27.14 rows=1,357 width=435) (actual time=3,899.123..4,343.817 rows=469,333 loops=1)

16.          

SubPlan (forGroupAggregate)

17. 5.224 106,241.794 ↑ 1.0 1 1,306

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

18. 0.000 106,236.570 ↑ 1.8 5 1,306

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

19. 218.102 105,429.462 ↓ 52.3 366 1,306

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

  • Group Key: a.student_id
20. 105,211.360 105,211.360 ↓ 52.3 366 1,306

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

  • Filter: (school_id = ac.school_id)
  • Rows Removed by Filter: 468967
21. 956.090 956.090 ↓ 0.0 0 478,045

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,045)

  • Index Cond: ((studentid = a.student_id) AND (testtypeid = 4))
22. 10.448 106,236.570 ↑ 1.0 1 1,306

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

23. 0.000 106,226.122 ↑ 2.7 33 1,306

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

24. 218.102 105,322.370 ↓ 52.3 366 1,306

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

  • Group Key: a_1.student_id
25. 105,104.268 105,104.268 ↓ 52.3 366 1,306

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

  • Filter: (school_id = ac.school_id)
  • Rows Removed by Filter: 468967
26. 956.090 956.090 ↓ 0.0 0 478,045

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,045)

  • Index Cond: ((studentid = a_1.student_id) AND (testtypeid = 5))
27. 28.732 108,350.984 ↑ 1.0 1 1,306

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

28. 135.530 108,322.252 ↑ 2.0 131 1,306

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

29. 220.714 105,318.452 ↓ 52.3 366 1,306

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

  • Group Key: a_2.student_id
30. 105,097.738 105,097.738 ↓ 52.3 366 1,306

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

  • Filter: (school_id = ac.school_id)
  • Rows Removed by Filter: 468967
31. 2,868.270 2,868.270 ↓ 0.0 0 478,045

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,045)

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

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

33. 204.895 107,071.104 ↓ 70.2 281 1,306

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

34. 220.714 105,432.074 ↓ 52.3 366 1,306

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

  • Group Key: a_3.student_id
35. 105,211.360 105,211.360 ↓ 52.3 366 1,306

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

  • Filter: (school_id = ac.school_id)
  • Rows Removed by Filter: 468967
36. 1,434.135 1,434.135 ↑ 1.0 1 478,045

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,045)

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

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

38. 23.361 106,768.112 ↑ 18.6 14 1,306

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

39. 216.796 105,310.616 ↓ 52.3 366 1,306

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

  • Group Key: a_4.student_id
40. 105,093.820 105,093.820 ↓ 52.3 366 1,306

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

  • Filter: (school_id = ac.school_id)
  • Rows Removed by Filter: 468967
41. 1,434.135 1,434.135 ↓ 0.0 0 478,045

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,045)

  • Index Cond: (studentid = a_4.student_id)
  • Filter: (topicgradeid > studentgradeid)
  • Rows Removed by Filter: 1
42. 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))
43. 0.000 0.000 ↓ 0.0 0

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

44. 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)
45. 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)
46. 0.206 0.206 ↓ 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.045..0.206 rows=72 loops=1)

  • Index Cond: (subjectid = 10)
47. 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))
48. 0.000 0.000 ↓ 0.0 0

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

49. 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)
50. 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)
51. 0.024 0.024 ↓ 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.008..0.024 rows=75 loops=1)

  • Index Cond: (subjectid = 9)
52. 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)
53. 10.718 10.718 ↓ 1.1 28,544 1

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

  • Filter: (subjectid = 11)
  • Rows Removed by Filter: 63486
54. 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)
55. 1.117 1.637 ↓ 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.561..1.637 rows=6,194 loops=1)

  • Recheck Cond: (subjectid = 8)
  • Heap Blocks: exact=402
56. 0.520 0.520 ↓ 1.2 6,574 1

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

  • Index Cond: (subjectid = 8)
57. 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)
58. 8.488 8.488 ↓ 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.488 rows=28,588 loops=1)

  • Filter: (subjectid = 2)
  • Rows Removed by Filter: 63442
59. 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)
60. 8.472 8.472 ↓ 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.007..8.472 rows=28,557 loops=1)

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