Massive SQL query for getting pipeline statuses is very slow
On some pages (e.g. ProjectsController#show
) we will run a query such as the following:
SELECT (CASE WHEN (SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha"))=(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'skipped') AND (false) THEN 'success' WHEN (SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha"))=(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'skipped') THEN 'skipped' WHEN (SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha"))=(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'success') THEN 'success' WHEN (SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha"))=(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'created') THEN 'created' WHEN (SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha"))=(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'success')+(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'skipped') THEN 'success' WHEN (SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha"))=(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'success')+(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'skipped')+(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'canceled') THEN 'canceled' WHEN (SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha"))=(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'created')+(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'skipped')+(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'pending') THEN 'pending' WHEN (SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'running')+(SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'pending')>0 THEN 'running' WHEN (SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'manual')>0 THEN 'manual' WHEN (SELECT count(*) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha") AND "ci_pipelines"."status" = 'created')>0 THEN 'running' ELSE 'failed' END) FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 13083 AND "ci_pipelines"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' 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"."sha" = 'b1fc58593b5f09bb0bf2dffc97924d8630d83bd3' AND "ci_pipelines"."ref" = 'master' GROUP BY "ci_pipelines"."ref", "ci_pipelines"."sha");
This query is both massive and slow, easily taking up between 10 and 20 milliseconds. We should optimise this query so it's both compact and fast. From what I gather this requires some changes scheduled for 10.1 to be made first, but the CI team should know more.
This blocks https://gitlab.com/gitlab-org/gitlab-ce/issues/36878
cc @ayufan