Skip to content

Add index for projects API default param

Andreas Brandl requested to merge ab/projects-api-indexes into master

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.

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 🤖

Merge request reports