Improve MergeRequest commit_notes query performance

Problem

The original issue was reported in https://gitlab.com/gitlab-com/request-for-help/-/work_items/4014

It seems that this query becomes quite slow when there are sizable commits on a merge request and the project has many notes.

    Note
      .user
      .where(project_id: [source_project_id, target_project_id])
      .for_commit_id(commit_ids)

(source)

We call this when looking up related notes during MergeRequest#show page load.

We feed up to 100 commits for this query and if the number of notes within a project is big the query becomes quite slow. sample query plan

We have index_notes_on_project_id_and_noteable_type index and it's being used, but it only covers project_id and noteable_type. Since we don't have any composite index that covers system and commit_id, the db has to filter each row one by one (especially with large number of commits_id with IN query) which can become slower as the number of notes within the project grows.

These issues happened ~800 times over the last two weeks across several projects. See https://log.gprd.gitlab.net/app/r/s/W6Dun

Possible solution

• Add composite index that covers all project_id, noteable_type, commit_id, system
• Find another way to query this more efficiently