Getting the most recent pipeline of a branch (e.g. master) is very slow
On a project's homepage (ProjectsController#show
) we will run a query like the following:
SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."status" = 'success' AND "ci_pipelines"."ref" = 'master' AND "ci_pipelines"."id" IN (SELECT max("ci_pipelines".id) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."status" = 'success' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") ORDER BY "ci_pipelines"."id" DESC LIMIT 1;
This query is used to get the latest pipeline of a ref. In the worst case this query can take 50 milliseconds to execute and will be the slowest query on this page.
As far as I can tell we can get the exact same results using the following much more simple query:
SELECT ci_pipelines.id
FROM ci_pipelines
WHERE ci_pipelines.project_id = 13083
AND ci_pipelines.status = 'success'
AND ci_pipelines.ref = 'master'
ORDER BY ci_pipelines.id DESC
LIMIT 1;
This query will take around 25 milliseconds but can be made even faster by indexing (project_id, ref, status, id)
and dropping the (project_id, ref, status)
index. This way we can do a regular backwards index scan, without the need for any bitmap scans or filters.
This blocks https://gitlab.com/gitlab-org/gitlab-ce/issues/36878