Short conclusion:
Switching from CTEs to temporary tables and analyzing reduced the runtime from 15 minutes to about 1.5 minutes.
Longer conclusion:
- I experimented w/ materializing the CTEs and it helped at the margins but did not significantly contribute to a reduction in runtime.
- No clustering was required because once I switched to temporary tables the new plan no longer used the for_upsert index.
- Increasing work_mem to 100MB (up from 4MB) helped at the margins (i.e., some 100's of millisecond improvement) but did not represent a significant reduction in the runtime.
- It wasn't obvious to me which window function would be appropriate for the problem I was trying to solve therefore I didn't experiment w/ that approach.
- The selectivity of score_name='student_performance_index' was not enough for the planner to choose an index over doing a FTS.
Finally, thank you both for helping me bring this poor performing query to heel. Your insights were helpful and greatly appreciated.
Sincerely,
Dane
On Tue, Feb 16, 2021 at 10:25 AM Dane Foster <studdugie@xxxxxxxxx> wrote:
On Tue, Feb 16, 2021 at 10:13 AM Michael Lewis <mlewis@xxxxxxxxxxx> wrote:Sort Method: external merge Disk: 30760kB
Worker 0: Sort Method: external merge Disk: 30760kB
Worker 1: Sort Method: external merge Disk: 30760kBIf 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!Sincerely,Dane
