Skip to content

Add indexes for authenticated project api calls

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

What does this MR do?

This is to support queries like the following:

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"."updated_at" DESC,
    "projects"."id" DESC
LIMIT 20 OFFSET 0;

With this change, we add indexes for all order_by options. The regularly sorted index (asc, asc) has the common name whereas the one with desc, desc is optimized for API calls. It may be subject to removal later (see #195881 (closed)).

A few indexes already existed. Others became redundant with the additional $column, id index and the migration removes the redundant index on $column.

Example plans (here for updated_at desc, id desc):

Relates to #36390 (closed).

A similar MR for unauthenticated calls is !22784 (merged).

Screenshots

Refer to #36390 (comment 250204180) for graphs and a query time heatmap.

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Additional overhead by the added indexes. We add 8 new indexes here with sizes 300-400 MB each. We also replace and extend existing indexes.

Edited by 🤖 GitLab Bot 🤖

Merge request reports