Prepare new index to improve related_notes query performance
What does this MR do and why?
This MR adds a new index index_notes_on_project_commit_system which indexes Commit notes with project_id, commit_id, and system to help with loading MergeRequest#related_notes when a MergeRequest has many commits (up to 100 commits). Currently, it seems that around 800 requests were timing out over the last 2 weeks.
I tested with CREATE INDEX index_notes_on_project_commit_system ON notes (project_id, commit_id) WHERE noteable_type = 'Commit' AND system = false; hoping to reduce the index size, but somehow that index wasn't performing well as postgres was still filtering out commit_id after the index lookup for some reason. I understand that we try not to create a new index on notes table due to ongoing WAL rate impact, but I think we need to consider adding this index to improve the query performance.
Before Change
Query plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/47402/commands/143514
Time: 1.137 min
After Change
Query plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/47402/commands/143522
Time: 373.340 ms
References
Screenshots or screen recordings
| Before | After |
|---|---|
How to set up and validate locally
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Related to #585822