Improve performance of filtering merge requests by an environment
What does this MR do?
When filtering merge requests by an environment name, the index used by DeploymentMergeRequest.deployed_to() is not ideal. We can improve performance by adding the following AND condition:
AND environments.project_id = merge_requests.target_project_id
This condition forces the query to use the index on merge_requests (project_id, name), instead of using the slower index (name varchar_pattern_ops).
This improves performance of queries such as this:
SELECT merge_requests.*
FROM merge_requests
WHERE merge_requests.target_project_id = 278964
AND EXISTS (
SELECT 1
FROM deployment_merge_requests
INNER JOIN deployments ON deployments.id = deployment_merge_requests.deployment_id
INNER JOIN environments ON environments.id = deployments.environment_id
WHERE deployment_merge_requests.merge_request_id = merge_requests.id
AND environments.name = 'gstg'
AND environments.project_id = merge_requests.target_project_id
)
ORDER BY merge_requests.id DESC
LIMIT 100;
For more information, refer to #270068 (comment 432945738).
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entryFeature is behind an unreleased feature flag -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Edited by Yorick Peterse