Optimize project deployments API endpoint when filtering by environment (!59223) · Merge requests · GitLab.org / GitLab

Michael Kozono requested to merge mk/fix-timeout-when-filtering-project-deployments-by-environment into master Apr 13, 2021

What does this MR do?

Improves the performance of the API endpoint List project deployments, when filtering by environment.

Before

SELECT
    "deployments".*
FROM
    "deployments"
    INNER JOIN "environments" ON "environments"."id" = "deployments"."environment_id"
WHERE
    "deployments"."project_id" = 14359413
    AND "deployments"."updated_at" <= '2021-04-03 08:00:00'
    AND "environments"."name" = 'prd'
ORDER BY
    "deployments"."iid" DESC
LIMIT 100 OFFSET 0;

This index was already added, which improved this very same query in most cases. But for some kinds of data, the query still performs poorly, and even times out with a cold cache. (Subsequent queries with warm cache are fine at 150ms, but the timeout condition occurs regularly with particular project bot requests, as shown in Kibana #325627 (comment 546860316).

https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/3432/commands/11382

After

The database doesn't know that the project_id that we are filtering deployments on can be used to filter environments as well, when we are filtering by environment name. There is already an index for this search: CREATE UNIQUE INDEX index_environments_on_project_id_and_name ON environments USING btree (project_id, name);

So, when project_id is available, filter environments by that project_id as well.

SELECT
    "deployments".*
FROM
    "deployments"
    INNER JOIN "environments" ON "environments"."id" = "deployments"."environment_id"
WHERE
    "deployments"."project_id" = 14359413
    AND "deployments"."updated_at" <= '2021-04-03 08:00:00'
    AND "environments"."name" = 'prd'
    AND "environments"."project_id" = 14359413
ORDER BY
    "deployments"."iid" DESC
LIMIT 100 OFFSET 0;