Skip to content

Add index for authenticated requests to projects API

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

What does this MR do?

This adds ideal indexes for the query executed by a GET /api/v4/projects for an authenticated user with otherwise default params (no additional filters, custom order-by).

The query is:

SELECT
    "projects".*
FROM
    "projects"
WHERE (EXISTS (
        SELECT
            1
        FROM
            "project_authorizations"
        WHERE
            "project_authorizations"."user_id" = 1562869
            AND (project_authorizations.project_id = projects.id))
        OR projects.visibility_level IN (10, 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 created_at, id.

We also remove the existing, now redundant index on created_at only.

Does this MR meet the acceptance criteria?

Conformity

Edited by 🤖 GitLab Bot 🤖

Merge request reports