Targeted query optimization for CI queueing
This is a follow-up to gitlab-com/gl-infra/production#4156 (comment 546835041).
Query
- Plan: https://explain.depesz.com/s/8NUQ
- Timings outside of the incident: This is taking 9s on a luke-warm cache and still 3.5s on a hot cache (replica).
SELECT
"ci_builds"."id"
FROM
"ci_builds"
INNER JOIN "projects" ON "projects"."id" = "ci_builds"."project_id"
LEFT JOIN project_features ON ci_builds.project_id = project_features.project_id
LEFT JOIN (
SELECT
"ci_builds"."project_id",
count(*) AS running_builds
FROM
"ci_builds"
WHERE
"ci_builds"."type" = 'Ci::Build'
AND ("ci_builds"."status" IN ('running'))
AND "ci_builds"."runner_id" IN (
SELECT
"ci_runners"."id"
FROM
"ci_runners"
WHERE
"ci_runners"."runner_type" = 1)
GROUP BY
"ci_builds"."project_id") AS project_builds ON ci_builds.project_id = project_builds.project_id
WHERE ("ci_builds"."status" IN ('pending'))
AND "ci_builds"."runner_id" IS NULL
AND "projects"."shared_runners_enabled" = TRUE
AND "projects"."pending_delete" = FALSE
AND (project_features.builds_access_level IS NULL
OR project_features.builds_access_level > 0)
AND "ci_builds"."type" = 'Ci::Build'
AND ("projects"."visibility_level" = 20
OR (EXISTS ( WITH RECURSIVE "base_and_ancestors" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE (namespaces.id = projects.namespace_id))
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_ancestors"
WHERE
"namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT DISTINCT
1
FROM
"base_and_ancestors" AS "namespaces"
LEFT JOIN namespace_statistics ON namespace_statistics.namespace_id = namespaces.id
WHERE
"namespaces"."parent_id" IS NULL
AND (COALESCE(namespaces.shared_runners_minutes_limit, 400, 0) = 0
OR COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE((namespaces.shared_runners_minutes_limit + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), (400 + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), 0) * 60))))
AND (NOT EXISTS (
SELECT
1
FROM
"taggings"
WHERE
"taggings"."taggable_type" = 'CommitStatus'
AND "taggings"."context" = 'tags'
AND (taggable_id = ci_builds.id)
AND "taggings"."tag_id" NOT IN (4060, 14174)))
ORDER BY
COALESCE(project_builds.running_builds, 0) ASC, ci_builds.id ASC
Edited by Andreas Brandl