Add index for projects API default param
What does this MR do?
This adds ideal indexes for the query executed by a GET /api/v4/projects
for an unauthenticated user with otherwise default params (no additional filters, custom order-by).
The query is:
SELECT
"projects".*
FROM
"projects"
WHERE
"projects"."visibility_level" = 20
AND "projects"."pending_delete" = FALSE
ORDER BY
"projects"."created_at" DESC,
"projects"."id" DESC
LIMIT 20 OFFSET 0
The index added here is on visibility_level, created_at, id
.
- Plan before: https://explain.depesz.com/s/O1hi
- Plan after: https://explain.depesz.com/s/0qP
We also remove the existing, now redundant index on visibility_level
only. The new index is roughly double the size (400 MB instead of 200 MB without bloat).
Does this MR meet the acceptance criteria?
Conformity
Edited by 🤖 GitLab Bot 🤖