API: `jobs/request`: remove CTE query from BIG QUERY for the quota calculation
Follow the: &5591 (comment 527956207)
EXPLAIN ANALYZE 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
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, 28535, 14174, 12091, 999, 11755, 11, 4485, 884, 7804)))
ORDER BY
COALESCE(project_builds.running_builds, 0) ASC,
ci_builds.id ASC Sort (cost=44663512.37..44663677.61 rows=66097 width=12) (actual time=1211.669..1211.741 rows=1372 loops=1)
Sort Key: (COALESCE(project_builds.running_builds, '0'::bigint)), ci_builds.id
Sort Method: quicksort Memory: 113kB
-> Hash Left Join (cost=145.76..44658220.55 rows=66097 width=12) (actual time=127.385..1210.393 rows=1372 loops=1)
Hash Cond: (ci_builds.project_id = project_builds.project_id)
-> Nested Loop (cost=2.27..44657903.54 rows=66097 width=8) (actual time=1.688..1083.682 rows=1372 loops=1)
-> Nested Loop Left Join (cost=1.71..749513.07 rows=121534 width=8) (actual time=0.441..715.831 rows=13024 loops=1)
Filter: ((project_features.builds_access_level IS NULL) OR (project_features.builds_access_level > 0))
-> Nested Loop Anti Join (cost=1.27..409757.28 rows=124616 width=8) (actual time=0.344..584.933 rows=13024 loops=1)
-> Index Scan using index_ci_builds_on_status_and_type_and_runner_id on ci_builds (cost=0.70..188183.09 rows=131559 width=8) (actual time=0.279..428.097 rows=18346 loops=1)
Index Cond: (((status)::text = 'pending'::text) AND ((type)::text = 'Ci::Build'::text) AND (runner_id IS NULL))
-> Index Scan using index_taggings_on_taggable_id_and_taggable_type_and_context on taggings (cost=0.57..9.14 rows=8 width=4) (actual time=0.008..0.008 rows=0 loops=18346)
Index Cond: ((taggable_id = ci_builds.id) AND ((taggable_type)::text = 'CommitStatus'::text) AND ((context)::text = 'tags'::text))
Filter: (tag_id <> ALL ('{4060,28535,14174,12091,999,11755,11,4485,884,7804}'::integer[]))
Rows Removed by Filter: 0
-> Index Scan using index_project_features_on_project_id on project_features (cost=0.44..2.71 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=13024)
Index Cond: (ci_builds.project_id = project_id)
-> Index Scan using projects_pkey on projects (cost=0.56..361.28 rows=1 width=4) (actual time=0.028..0.028 rows=0 loops=13024)
Index Cond: (id = ci_builds.project_id)
Filter: (shared_runners_enabled AND (NOT pending_delete) AND ((visibility_level = 20) OR (SubPlan 2)))
Rows Removed by Filter: 1
SubPlan 2
-> Nested Loop Left Join (cost=353.15..358.21 rows=1 width=0) (actual time=0.024..0.024 rows=0 loops=9281)
Filter: ((COALESCE(namespaces_2.shared_runners_minutes_limit, 400) = 0) OR (COALESCE(namespace_statistics.shared_runners_seconds, 0) < (COALESCE((namespaces_2.shared_runners_minutes_limit + COALESCE(namespaces_2.extra_shared_runners_minutes_limit, 0)), (400 + COALESCE(namespaces_2.extra_shared_runners_minutes_limit, 0)), 0) * 60)))
Rows Removed by Filter: 1
CTE base_and_ancestors
-> Recursive Union (cost=0.43..352.72 rows=101 width=344) (actual time=0.011..0.014 rows=1 loops=9281)
-> Index Scan using namespaces_pkey on namespaces (cost=0.43..3.45 rows=1 width=344) (actual time=0.009..0.009 rows=1 loops=9281)
Index Cond: (id = projects.namespace_id)
-> Nested Loop (cost=0.43..34.73 rows=10 width=344) (actual time=0.002..0.002 rows=0 loops=9097)
-> WorkTable Scan on base_and_ancestors (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=1 loops=9097)
-> Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.001..0.001 rows=0 loops=9097)
Index Cond: (id = base_and_ancestors.parent_id)
-> CTE Scan on base_and_ancestors namespaces_2 (cost=0.00..2.02 rows=1 width=12) (actual time=0.014..0.015 rows=1 loops=9281)
Filter: (parent_id IS NULL)
Rows Removed by Filter: 0
-> Index Scan using index_namespace_statistics_on_namespace_id on namespace_statistics (cost=0.42..3.44 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=9281)
Index Cond: (namespace_id = namespaces_2.id)
-> Hash (cost=143.46..143.46 rows=2 width=12) (actual time=125.672..125.672 rows=904 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 47kB
-> Subquery Scan on project_builds (cost=143.41..143.46 rows=2 width=12) (actual time=124.288..125.440 rows=904 loops=1)
-> GroupAggregate (cost=143.41..143.44 rows=2 width=12) (actual time=124.287..125.250 rows=904 loops=1)
Group Key: ci_builds_1.project_id
-> Sort (cost=143.41..143.41 rows=2 width=4) (actual time=124.274..124.622 rows=2574 loops=1)
Sort Key: ci_builds_1.project_id
Sort Method: quicksort Memory: 217kB
-> Nested Loop (cost=1.25..143.40 rows=2 width=4) (actual time=0.283..123.177 rows=2574 loops=1)
-> Index Scan using index_ci_runners_on_runner_type on ci_runners (cost=0.55..26.69 rows=18 width=4) (actual time=0.136..0.553 rows=15 loops=1)
Index Cond: (runner_type = 1)
-> Index Scan using index_ci_builds_on_status_and_type_and_runner_id on ci_builds ci_builds_1 (cost=0.70..6.45 rows=3 width=8) (actual time=0.106..8.141 rows=172 loops=15)
Index Cond: (((status)::text = 'running'::text) AND ((type)::text = 'Ci::Build'::text) AND (runner_id = ci_runners.id))
Planning Time: 34.657 ms
Execution Time: 1212.978 ms
(53 rows)
Edited by Kamil Trzciński