Skip to content

Indexes for Project API

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

What does this MR do?

This MR adds more indexes to projects to support unauthenticated calls to the projects API better. The related issue is: #36390 (comment 250204180)

This is a follow-up with the same reasoning as in !22623 (merged). Please refer to this MR for more details on why we need two indexes at the moment per order_by.

We add two indexes per each order_by option for name, path, updated_at, last_activity_at.

The indexes are partial for the visibility_level=20 condition. This brings index size down to about 50 MB per index. Because of the partial condition, the indexes only support unauthenticated API calls. For authenticated calls, I'll send another MR with more index additions.

As another example, this goes for the last_activity_at order - the pattern is always the same for any of these options, though:

SELECT
    "projects".*
FROM
    "projects"
WHERE
    "projects"."visibility_level" = 20
    AND "projects"."pending_delete" = FALSE
ORDER BY
    "projects"."last_activity_at" ASC,
    "projects"."id" DESC
LIMIT 20 OFFSET 0

Plan without index: https://explain.depesz.com/s/DVjq Plan with index: https://explain.depesz.com/s/oqCr

Very similar for the last_activity_at DESC, id DESC sort order. Here, we have an index on last_activity_at, id which we scan backwards for this query.

If #195881 (closed) is an acceptable change, we can simply drop the indexes on $column, id desc as the other index is now usable from both directions.

Notes for validation:

A request like this takes about 11s:

$ time curl -v https://gitlab.com/api/v4/projects\?order_by\=updated_at\&sort\=asc
...
< X-Request-Id: H3C9HdCcEa7                                                                                                                   < X-Runtime: 11.231580 
...
0,07s user 0,01s system 0% cpu 11,940 total

The request log suggests that most of that time is spent in the database.

Screenshots

This change (together with !22623 (merged)) should change this picture drastically:

unauthenticated

(TODO: Once available add new heatmap here)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

This change adds 8 indexes with about 50 MB each, which is only an increase in total index size for projects in order of 1%.

Edited by 🤖 GitLab Bot 🤖

Merge request reports