Create index on environments by state
What does this MR do?
This adds an index on environments (project_id, state)
, motivated by the plans posted in https://gitlab.com/gitlab-org/gitlab-ce/issues/27644#note_190195075 (cc @krasio ).
On the database-lab instance, this brings down query runtime from about 300ms to 22ms:
SELECT
COALESCE(environment_type, name) AS folder,
COUNT(*) AS size,
MAX(id) AS last_id
FROM
"environments"
WHERE
"environments"."project_id" = 13083
AND ("environments"."state" IN ('available'))
GROUP BY
COALESCE(environment_type, name)
ORDER BY
folder ASC
LIMIT 20 OFFSET 0
Plan before: https://explain.depesz.com/s/BNZV Plan after: https://explain.depesz.com/s/X2Rb
Conformity
-
Changelog entry for user-facing changes, or community contribution. Check the link for other scenarios. - [-] Documentation created/updated or follow-up review issue created
- [-] Code review guidelines
- [-] Merge request performance guidelines
- [-] Style guides
-
Database guides - [-] Separation of EE specific content
Edited by Mayra Cabrera