If you can increase work_mem, even setting it temporarily higher for the session or transaction, that may dramatically change the plan.
I will try increasing work_mem for the session later today.
The advice given by Justin particularly about row estimates would be wise to pursue.
I'd wonder how selective that condition of score_name = 'student_performance_index' is in filtering out many of the 9.3 million tuples in that table and if an index with that as the leading column, or just an index on that column would be helpful.
There are 1,206,355 rows where score_name='student_performance_idex'.
You'd need to look at pg_stats for the table and see how many distinct values, and if student_performance_index is relatively high or low (or not present) in the MCVs list.
I will look into that.
I am not sure if your query does what you want it to do as I admit I didn't follow your explanation of the desired behavior. My hunch is that you want to make use of a window function and get rid of one of the CTEs.
If you could tell me what part(s) are unclear I would appreciate it so that I can write a better comment.
Thank you sooo much for all the feedback. It is greatly appreciated!
