Querying merge requests using the merge commit SHA is slow
Discovered in https://gitlab.com/gitlab-org/gitlab-ce/issues/37814
When displaying a merge request we end up running a query along the lines of the following:
SELECT "merge_requests".*
FROM "merge_requests"
WHERE "merge_requests"."deleted_at" IS NULL
AND "merge_requests"."target_project_id" = 13083
AND "merge_requests"."merge_commit_sha" = 'e80a893ff0ea8466099f6478183631af55933db2'
ORDER BY "merge_requests"."id" DESC
LIMIT 1;
Because merge_commit_sha
is not indexed we end up running a Filter
instead of a regular index scan. Further we have to perform a Sort, instead of a backwards index scan.
To improve this we'd have to:
- Get rid of the
deleted_at
filter - Add an index on
(target_project_id, merge_commit_sha, id)
-
Remove the standalone index onThere is no such indextarget_project_id
since queries using said index can use the one we added in the previous step
See https://gitlab.com/gitlab-org/gitlab-ce/issues/37814#note_40870711 for more information.