GraphQL: Add membership filter to ProjectRunnersResolver
What does this MR do and why?
This MR aims to close #383510 and #328808.
Screenshots or screen recordings
header | header |
---|---|
nil | |
DIRECT |
|
OWNED_OR_INSTANCE_WIDE |
|
PARENT_GROUPS |
How to set up and validate locally
-
Go to the shell in your GDK
gitlab
directory and runbundle exec rake "gitlab:seed:runner_fleet"
. This will seed your GDK with some runners and jobs required for testing this MR. -
Use this query, and change the
membership
param:query projectFinder { project( fullPath: "rf-top-level-group-1/rf-group-1.1/rf-group-1.1.1/rf-project-1-1-1-1" ) { id fullPath runners(membership: PARENT_GROUPS) { count } } }
or
query projectFinder { project( fullPath: "rf2-top-level-group-1/rf2-group-1.1/rf2-group-1.1.1/rf2-project-1-1-1-1" ) { id fullPath runners( membership: DIRECT paused: false status: NEVER_CONTACTED type: PROJECT_TYPE tagList: ["shell"] upgradeStatus: RECOMMENDED search: "R" sort: CREATED_DESC ) { nodes { id } } } }
-
Notice the count of runners changes with different
membership
params.
Database query plans
DIRECT
active=true
project.runners query plan
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_projects" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
WHERE "ci_runner_projects"."project_id" = 29102510
AND "ci_runners"."active" = TRUE
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 101
https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58529
Limit (cost=6248.15..6248.40 rows=101 width=283) (actual time=3878.849..3878.876 rows=101 loops=1)
Buffers: shared hit=2587 read=3134 dirtied=504
I/O Timings: read=3802.342 write=0.000
-> Sort (cost=6248.15..6251.32 rows=1268 width=283) (actual time=3878.848..3878.865 rows=101 loops=1)
Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
Sort Method: top-N heapsort Memory: 77kB
Buffers: shared hit=2587 read=3134 dirtied=504
I/O Timings: read=3802.342 write=0.000
-> Nested Loop (cost=0.86..6199.60 rows=1268 width=283) (actual time=28.119..3872.526 rows=1142 loops=1)
Buffers: shared hit=2581 read=3134 dirtied=504
I/O Timings: read=3802.342 write=0.000
-> Index Scan using index_ci_runner_projects_on_project_id on public.ci_runner_projects (cost=0.43..1735.75 rows=1303 width=4) (actual time=13.843..1226.729 rows=1142 loops=1)
Index Cond: (ci_runner_projects.project_id = 29102510)
Buffers: shared hit=134 read=1013 dirtied=47
I/O Timings: read=1210.544 write=0.000
-> Index Scan using index_ci_runners_on_active on public.ci_runners (cost=0.43..3.43 rows=1 width=283) (actual time=2.310..2.310 rows=1 loops=1142)
Index Cond: ((ci_runners.active = true) AND (ci_runners.id = ci_runner_projects.runner_id))
Buffers: shared hit=2447 read=2121 dirtied=457
I/O Timings: read=2591.799 write=0.000
full query
project.runners query plan
SELECT COUNT(*)
FROM "ci_runners"
INNER JOIN "ci_runner_projects" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
INNER JOIN "ci_runner_versions" "runner_version" ON "runner_version"."version" = "ci_runners"."version"
INNER JOIN "taggings" "ci::runner_taggings_ec122f5" ON "ci::runner_taggings_ec122f5"."taggable_id" = "ci_runners"."id"
AND "ci::runner_taggings_ec122f5"."taggable_type" = 'Ci::Runner'
AND "ci::runner_taggings_ec122f5"."tag_id" IN (
SELECT "tags"."id"
FROM "tags"
WHERE "tags"."name" LIKE 'shell' ESCAPE '!')
WHERE "ci_runner_projects"."project_id" = 29102510
AND ("ci_runners"."token" = 'R'
OR "ci_runners"."description" ILIKE 'R')
AND "ci_runners"."active" = TRUE
AND "ci_runners"."contacted_at" IS NULL
AND "runner_version"."status" = 3
AND "ci_runners"."runner_type" = 1
https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58532
Aggregate (cost=99.06..99.07 rows=1 width=8) (actual time=0.219..0.220 rows=1 loops=1)
Buffers: shared hit=84
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=2.15..99.05 rows=1 width=0) (actual time=0.217..0.218 rows=0 loops=1)
Buffers: shared hit=84
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1.72..98.58 rows=1 width=4) (actual time=0.217..0.217 rows=0 loops=1)
Buffers: shared hit=84
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1.44..95.28 rows=1 width=11) (actual time=0.216..0.217 rows=0 loops=1)
Buffers: shared hit=84
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1.01..94.81 rows=1 width=23) (actual time=0.216..0.217 rows=0 loops=1)
Buffers: shared hit=84
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners (cost=0.43..91.20 rows=1 width=11) (actual time=0.216..0.216 rows=0 loops=1)
Index Cond: (ci_runners.runner_type = 1)
Filter: (ci_runners.active AND (ci_runners.contacted_at IS NULL) AND (((ci_runners.token)::text = 'R'::text) OR ((ci_runners.description)::text ~~* 'R'::text)))
Rows Removed by Filter: 77
Buffers: shared hit=84
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_taggings_on_taggable_id_and_taggable_type_and_context on public.taggings ci::runner_taggings_ec122f5 (cost=0.58..3.60 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (("ci::runner_taggings_ec122f5".taggable_id = ci_runners.id) AND (("ci::runner_taggings_ec122f5".taggable_type)::text = 'Ci::Runner'::text))
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_unique_ci_runner_projects_on_runner_id_and_project_id on public.ci_runner_projects (cost=0.43..0.45 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((ci_runner_projects.runner_id = "ci::runner_taggings_ec122f5".taggable_id) AND (ci_runner_projects.project_id = 29102510))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version (cost=0.28..3.30 rows=1 width=21) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((runner_version.status = 3) AND (runner_version.version = (ci_runners.version)::text))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using tags_pkey on public.tags (cost=0.43..0.45 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (tags.id = "ci::runner_taggings_ec122f5".tag_id)
Filter: ((tags.name)::text ~~ 'shell'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
OWNED_OR_INSTANCE_WIDE
active=true
::Group.joins(:projects).where(projects: { id: project_id }).self_and_ancestors query plan
WITH "base_ancestors_cte" AS MATERIALIZED (
SELECT "namespaces"."traversal_ids"
FROM "namespaces"
INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "projects"."id" = 29102510
)
SELECT "id"
FROM "namespaces"
INNER JOIN ( SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids")
FROM base_ancestors_cte) AS ancestors (ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'
https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58534
Nested Loop (cost=6.87..20.53 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
CTE base_ancestors_cte
-> Nested Loop (cost=0.27..6.64 rows=1 width=28) (actual time=0.009..0.010 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects (cost=0.14..3.16 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (projects.id = 29102510)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_groups_on_path_and_id on public.namespaces namespaces_1 (cost=0.12..3.14 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_1.id = projects.namespace_id)
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=0.10..0.23 rows=10 width=4) (actual time=0.011..0.012 rows=0 loops=1)
Group Key: unnest(base_ancestors_cte.traversal_ids)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> ProjectSet (cost=0.00..0.08 rows=10 width=4) (actual time=0.010..0.011 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_ancestors_cte (cost=0.00..0.02 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_groups_on_path_and_id on public.namespaces (cost=0.12..1.34 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces.id = (unnest(base_ancestors_cte.traversal_ids)))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
owned_or_instance_wide query plan 🔴
SELECT "ci_runners".*
FROM ((
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_projects"."project_id" = 29102510)
UNION ALL (
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_namespaces"."namespace_id" IN (13132392, 13132068, 5849309, 3730788))
UNION ALL (
SELECT "ci_runners".*
FROM "ci_runners"
WHERE "ci_runners"."runner_type" = 3)) ci_runners
WHERE "ci_runners"."active" = TRUE
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 101
https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58544
Limit (cost=469970.49..469982.27 rows=101 width=283) (actual time=6287.854..6372.831 rows=101 loops=1)
Buffers: shared hit=700001 read=144131 dirtied=39879
I/O Timings: read=9931.832 write=0.000
-> Gather Merge (cost=469970.49..892572.26 rows=3622048 width=283) (actual time=6287.848..6372.806 rows=101 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=700001 read=144131 dirtied=39879
I/O Timings: read=9931.832 write=0.000
-> Sort (cost=468970.46..473498.02 rows=1811024 width=283) (actual time=6280.392..6280.487 rows=68 loops=3)
Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
Sort Method: top-N heapsort Memory: 92kB
Buffers: shared hit=700001 read=144131 dirtied=39879
I/O Timings: read=9931.832 write=0.000
-> Parallel Append (cost=0.85..399624.44 rows=1811024 width=283) (actual time=7.112..5824.036 rows=484501 loops=3)
Buffers: shared hit=699967 read=144131 dirtied=39879
I/O Timings: read=9931.832 write=0.000
-> Nested Loop (cost=0.85..77.12 rows=14 width=283) (actual time=1.290..1.333 rows=0 loops=1)
Buffers: shared hit=8 read=8
I/O Timings: read=1.216 write=0.000
-> Index Scan using index_ci_runner_namespaces_on_namespace_id on public.ci_runner_namespaces (cost=0.42..28.85 rows=14 width=4) (actual time=1.289..1.298 rows=0 loops=1)
Index Cond: (ci_runner_namespaces.namespace_id = ANY ('{13132392,13132068,5849309,3730788}'::integer[]))
Buffers: shared hit=8 read=8
I/O Timings: read=1.216 write=0.000
-> Index Scan using ci_runners_pkey on public.ci_runners (cost=0.43..3.45 rows=1 width=283) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runners.id = ci_runner_namespaces.runner_id)
Filter: ci_runners.active
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=22.96..5439.39 rows=746 width=283) (actual time=14.209..1241.003 rows=1142 loops=1)
Buffers: shared hit=3084 read=2500 dirtied=223
I/O Timings: read=1194.835 write=0.000
-> Parallel Bitmap Heap Scan on public.ci_runner_projects (cost=22.53..2815.21 rows=766 width=4) (actual time=9.341..748.702 rows=1142 loops=1)
Buffers: shared hit=1 read=1014 dirtied=46
I/O Timings: read=734.416 write=0.000
-> Bitmap Index Scan using index_ci_runner_projects_on_project_id (cost=0.00..22.20 rows=1303 width=0) (actual time=7.089..7.096 rows=1144 loops=1)
Index Cond: (ci_runner_projects.project_id = 29102510)
Buffers: shared hit=1 read=7
I/O Timings: read=6.054 write=0.000
-> Index Scan using index_ci_runners_on_active on public.ci_runners ci_runners_1 (cost=0.43..3.43 rows=1 width=283) (actual time=0.426..0.426 rows=1 loops=1142)
Index Cond: ((ci_runners_1.active = true) AND (ci_runners_1.id = ci_runner_projects.runner_id))
Buffers: shared hit=3083 read=1486 dirtied=177
I/O Timings: read=460.419 write=0.000
-> Parallel Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_2 (cost=0.43..366938.91 rows=754213 width=283) (actual time=1.972..5350.958 rows=484120 loops=3)
Index Cond: (ci_runners_2.runner_type = 3)
Filter: ci_runners_2.active
Rows Removed by Filter: 14041
Buffers: shared hit=696875 read=141623 dirtied=39656
I/O Timings: read=8735.780 write=0.000
full query
::Group.joins(:projects).where(projects: { id: project_id }).self_and_ancestors query plan
WITH "base_ancestors_cte" AS MATERIALIZED (
SELECT "namespaces"."traversal_ids"
FROM "namespaces"
INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "projects"."id" = 29102510
)
SELECT "id"
FROM "namespaces"
INNER JOIN ( SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids")
FROM base_ancestors_cte) AS ancestors (ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'
https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58534
Nested Loop (cost=6.87..20.53 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
CTE base_ancestors_cte
-> Nested Loop (cost=0.27..6.64 rows=1 width=28) (actual time=0.009..0.010 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects (cost=0.14..3.16 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (projects.id = 29102510)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_groups_on_path_and_id on public.namespaces namespaces_1 (cost=0.12..3.14 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_1.id = projects.namespace_id)
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=0.10..0.23 rows=10 width=4) (actual time=0.011..0.012 rows=0 loops=1)
Group Key: unnest(base_ancestors_cte.traversal_ids)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> ProjectSet (cost=0.00..0.08 rows=10 width=4) (actual time=0.010..0.011 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_ancestors_cte (cost=0.00..0.02 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_groups_on_path_and_id on public.namespaces (cost=0.12..1.34 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces.id = (unnest(base_ancestors_cte.traversal_ids)))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
owned_or_instance_wide
SELECT COUNT(*)
FROM ((
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_projects"."project_id" = 29102510)
UNION ALL (
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_namespaces"."namespace_id" IN (13132392, 13132068, 5849309, 3730788))
UNION ALL (
SELECT "ci_runners".*
FROM "ci_runners"
WHERE "ci_runners"."runner_type" = 1)) ci_runners
INNER JOIN "ci_runner_versions" "runner_version" ON "runner_version"."version" = "ci_runners"."version"
INNER JOIN "taggings" "ci::runner_taggings_ec122f5" ON "ci::runner_taggings_ec122f5"."taggable_id" = "ci_runners"."id"
AND "ci::runner_taggings_ec122f5"."taggable_type" = 'Ci::Runner'
AND "ci::runner_taggings_ec122f5"."tag_id" IN (
SELECT "tags"."id"
FROM "tags"
WHERE "tags"."name" LIKE 'shell' ESCAPE '!')
WHERE ("ci_runners"."token" = 'R'
OR "ci_runners"."description" ILIKE 'R')
AND "ci_runners"."active" = TRUE
AND "ci_runners"."contacted_at" IS NULL
AND "runner_version"."status" = 3
AND "ci_runners"."runner_type" = 3
https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58547
Aggregate (cost=671.70..671.71 rows=1 width=8) (actual time=43.333..43.342 rows=1 loops=1)
Buffers: shared hit=161 read=558 dirtied=93
I/O Timings: read=34.311 write=0.000
-> Nested Loop (cost=226.59..671.69 rows=1 width=0) (actual time=43.330..43.338 rows=0 loops=1)
Buffers: shared hit=161 read=558 dirtied=93
I/O Timings: read=34.311 write=0.000
-> Nested Loop (cost=226.17..671.22 rows=1 width=4) (actual time=43.329..43.336 rows=0 loops=1)
Buffers: shared hit=161 read=558 dirtied=93
I/O Timings: read=34.311 write=0.000
-> Nested Loop (cost=225.89..668.92 rows=1 width=11) (actual time=43.328..43.336 rows=0 loops=1)
Buffers: shared hit=161 read=558 dirtied=93
I/O Timings: read=34.311 write=0.000
-> Append (cost=225.31..658.09 rows=3 width=11) (actual time=43.328..43.335 rows=0 loops=1)
Buffers: shared hit=161 read=558 dirtied=93
I/O Timings: read=34.311 write=0.000
-> Subquery Scan on *SELECT* 1 (cost=225.31..489.46 rows=1 width=11) (actual time=42.806..42.810 rows=0 loops=1)
Buffers: shared hit=154 read=550 dirtied=93
I/O Timings: read=33.882 write=0.000
-> Nested Loop (cost=225.31..489.45 rows=1 width=3340) (actual time=42.805..42.808 rows=0 loops=1)
Buffers: shared hit=154 read=550 dirtied=93
I/O Timings: read=33.882 write=0.000
-> Bitmap Heap Scan on public.ci_runners (cost=224.88..421.73 rows=34 width=11) (actual time=42.804..42.806 rows=0 loops=1)
Filter: (ci_runners.active AND (ci_runners.contacted_at IS NULL) AND (ci_runners.runner_type = 3))
Rows Removed by Filter: 15
Buffers: shared hit=154 read=550 dirtied=93
I/O Timings: read=33.882 write=0.000
-> BitmapOr (cost=224.88..224.88 rows=124 width=0) (actual time=25.136..25.137 rows=0 loops=1)
Buffers: shared hit=154 read=301
I/O Timings: read=20.985 write=0.000
-> Bitmap Index Scan using index_uniq_ci_runners_on_token (cost=0.00..1.94 rows=1 width=0) (actual time=0.266..0.267 rows=0 loops=1)
Index Cond: ((ci_runners.token)::text = 'R'::text)
Buffers: shared read=3
I/O Timings: read=0.242 write=0.000
-> Bitmap Index Scan using index_ci_runners_on_description_trigram (cost=0.00..222.92 rows=123 width=0) (actual time=24.867..24.867 rows=252 loops=1)
Index Cond: ((ci_runners.description)::text ~~* 'R'::text)
Buffers: shared hit=154 read=298
I/O Timings: read=20.743 write=0.000
-> Index Only Scan using index_unique_ci_runner_projects_on_runner_id_and_project_id on public.ci_runner_projects (cost=0.43..1.99 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((ci_runner_projects.runner_id = ci_runners.id) AND (ci_runner_projects.project_id = 29102510))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on *SELECT* 2 (cost=0.85..77.41 rows=1 width=11) (actual time=0.517..0.518 rows=0 loops=1)
Buffers: shared hit=7 read=8
I/O Timings: read=0.429 write=0.000
-> Nested Loop (cost=0.85..77.40 rows=1 width=3340) (actual time=0.515..0.516 rows=0 loops=1)
Buffers: shared hit=7 read=8
I/O Timings: read=0.429 write=0.000
-> Index Scan using index_ci_runner_namespaces_on_namespace_id on public.ci_runner_namespaces (cost=0.42..28.85 rows=14 width=4) (actual time=0.513..0.514 rows=0 loops=1)
Index Cond: (ci_runner_namespaces.namespace_id = ANY ('{13132392,13132068,5849309,3730788}'::integer[]))
Buffers: shared hit=7 read=8
I/O Timings: read=0.429 write=0.000
-> Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_1 (cost=0.43..3.46 rows=1 width=11) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((ci_runners_1.runner_type = 3) AND (ci_runners_1.id = ci_runner_namespaces.runner_id))
Filter: (ci_runners_1.active AND (ci_runners_1.contacted_at IS NULL) AND (((ci_runners_1.token)::text = 'R'::text) OR ((ci_runners_1.description)::text ~~* 'R'::text)))
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on *SELECT* 3 (cost=0.43..91.21 rows=1 width=11) (actual time=0.002..0.003 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> Result (cost=0.43..91.20 rows=1 width=3340) (actual time=0.001..0.002 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_2 (cost=0.43..91.20 rows=1 width=11) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runners_2.runner_type = 1)
Filter: (ci_runners_2.active AND (ci_runners_2.contacted_at IS NULL) AND (((ci_runners_2.token)::text = 'R'::text) OR ((ci_runners_2.description)::text ~~* 'R'::text)))
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_taggings_on_taggable_id_and_taggable_type_and_context on public.taggings ci::runner_taggings_ec122f5 (cost=0.58..3.60 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (("ci::runner_taggings_ec122f5".taggable_id = "*SELECT* 1".id) AND (("ci::runner_taggings_ec122f5".taggable_type)::text = 'Ci::Runner'::text))
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version (cost=0.28..2.30 rows=1 width=21) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((runner_version.status = 3) AND (runner_version.version = ("*SELECT* 1".version)::text))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using tags_pkey on public.tags (cost=0.43..0.45 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (tags.id = "ci::runner_taggings_ec122f5".tag_id)
Filter: ((tags.name)::text ~~ 'shell'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
PARENT_GROUPS
active=true
::Group.joins(:projects).where(projects: { id: project_id }).self_and_ancestors query plan
WITH "base_ancestors_cte" AS MATERIALIZED (
SELECT "namespaces"."traversal_ids"
FROM "namespaces"
INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "projects"."id" = 29102510
)
SELECT "id"
FROM "namespaces"
INNER JOIN ( SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids")
FROM base_ancestors_cte) AS ancestors (ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'
https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58534
Nested Loop (cost=6.87..20.53 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
CTE base_ancestors_cte
-> Nested Loop (cost=0.27..6.64 rows=1 width=28) (actual time=0.009..0.010 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects (cost=0.14..3.16 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (projects.id = 29102510)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_groups_on_path_and_id on public.namespaces namespaces_1 (cost=0.12..3.14 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_1.id = projects.namespace_id)
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=0.10..0.23 rows=10 width=4) (actual time=0.011..0.012 rows=0 loops=1)
Group Key: unnest(base_ancestors_cte.traversal_ids)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> ProjectSet (cost=0.00..0.08 rows=10 width=4) (actual time=0.010..0.011 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_ancestors_cte (cost=0.00..0.02 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_groups_on_path_and_id on public.namespaces (cost=0.12..1.34 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces.id = (unnest(base_ancestors_cte.traversal_ids)))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
belonging_to_group (called from belonging_to_parent_group_of_project) query plan
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_namespaces"."namespace_id" IN (13132392, 13132068, 5849309, 3730788)
AND "ci_runners"."active" = TRUE
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 101
https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58540
Limit (cost=77.38..77.42 rows=14 width=283) (actual time=0.087..0.088 rows=0 loops=1)
Buffers: shared hit=21
I/O Timings: read=0.000 write=0.000
-> Sort (cost=77.38..77.42 rows=14 width=283) (actual time=0.086..0.087 rows=0 loops=1)
Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=21
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.85..77.12 rows=14 width=283) (actual time=0.057..0.058 rows=0 loops=1)
Buffers: shared hit=15
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runner_namespaces_on_namespace_id on public.ci_runner_namespaces (cost=0.42..28.85 rows=14 width=4) (actual time=0.056..0.056 rows=0 loops=1)
Index Cond: (ci_runner_namespaces.namespace_id = ANY ('{13132392,13132068,5849309,3730788}'::integer[]))
Buffers: shared hit=15
I/O Timings: read=0.000 write=0.000
-> Index Scan using ci_runners_pkey on public.ci_runners (cost=0.43..3.45 rows=1 width=283) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runners.id = ci_runner_namespaces.runner_id)
Filter: ci_runners.active
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
full query
::Group.joins(:projects).where(projects: { id: project_id }).self_and_ancestors query plan
WITH "base_ancestors_cte" AS MATERIALIZED (
SELECT "namespaces"."traversal_ids"
FROM "namespaces"
INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "projects"."id" = 29102510
)
SELECT "id"
FROM "namespaces"
INNER JOIN ( SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids")
FROM base_ancestors_cte) AS ancestors (ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'
https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58534
Nested Loop (cost=6.87..20.53 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
CTE base_ancestors_cte
-> Nested Loop (cost=0.27..6.64 rows=1 width=28) (actual time=0.009..0.010 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects (cost=0.14..3.16 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (projects.id = 29102510)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_groups_on_path_and_id on public.namespaces namespaces_1 (cost=0.12..3.14 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_1.id = projects.namespace_id)
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=0.10..0.23 rows=10 width=4) (actual time=0.011..0.012 rows=0 loops=1)
Group Key: unnest(base_ancestors_cte.traversal_ids)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> ProjectSet (cost=0.00..0.08 rows=10 width=4) (actual time=0.010..0.011 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_ancestors_cte (cost=0.00..0.02 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Buffers: shared hit=2
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_groups_on_path_and_id on public.namespaces (cost=0.12..1.34 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces.id = (unnest(base_ancestors_cte.traversal_ids)))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
belonging_to_group (called from belonging_to_parent_group_of_project) query plan
SELECT COUNT(*)
FROM ((
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_projects"."project_id" = 29102510)
UNION ALL (
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_namespaces"."namespace_id" IN (13132392, 13132068, 5849309, 3730788))
UNION ALL (
SELECT "ci_runners".*
FROM "ci_runners"
WHERE "ci_runners"."runner_type" = 1)) ci_runners
INNER JOIN "ci_runner_versions" "runner_version" ON "runner_version"."version" = "ci_runners"."version"
INNER JOIN "taggings" "ci::runner_taggings_ec122f5" ON "ci::runner_taggings_ec122f5"."taggable_id" = "ci_runners"."id"
AND "ci::runner_taggings_ec122f5"."taggable_type" = 'Ci::Runner'
AND "ci::runner_taggings_ec122f5"."tag_id" IN (
SELECT "tags"."id"
FROM "tags"
WHERE "tags"."name" LIKE 'shell' ESCAPE '!')
WHERE ("ci_runners"."token" = 'R'
OR "ci_runners"."description" ILIKE 'R')
AND "ci_runners"."active" = TRUE
AND "ci_runners"."contacted_at" IS NULL
AND "runner_version"."status" = 3
AND "ci_runners"."runner_type" = 3
https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58541
Aggregate (cost=671.70..671.71 rows=1 width=8) (actual time=8.477..8.484 rows=1 loops=1)
Buffers: shared hit=719
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=226.59..671.69 rows=1 width=0) (actual time=8.473..8.480 rows=0 loops=1)
Buffers: shared hit=719
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=226.17..671.22 rows=1 width=4) (actual time=8.473..8.479 rows=0 loops=1)
Buffers: shared hit=719
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=225.89..668.92 rows=1 width=11) (actual time=8.472..8.478 rows=0 loops=1)
Buffers: shared hit=719
I/O Timings: read=0.000 write=0.000
-> Append (cost=225.31..658.09 rows=3 width=11) (actual time=8.472..8.477 rows=0 loops=1)
Buffers: shared hit=719
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on *SELECT* 1 (cost=225.31..489.46 rows=1 width=11) (actual time=8.361..8.363 rows=0 loops=1)
Buffers: shared hit=704
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=225.31..489.45 rows=1 width=3340) (actual time=8.360..8.362 rows=0 loops=1)
Buffers: shared hit=704
I/O Timings: read=0.000 write=0.000
-> Bitmap Heap Scan on public.ci_runners (cost=224.88..421.73 rows=34 width=11) (actual time=8.358..8.360 rows=0 loops=1)
Filter: (ci_runners.active AND (ci_runners.contacted_at IS NULL) AND (ci_runners.runner_type = 3))
Rows Removed by Filter: 15
Buffers: shared hit=704
I/O Timings: read=0.000 write=0.000
-> BitmapOr (cost=224.88..224.88 rows=124 width=0) (actual time=4.750..4.751 rows=0 loops=1)
Buffers: shared hit=455
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan using index_uniq_ci_runners_on_token (cost=0.00..1.94 rows=1 width=0) (actual time=0.047..0.047 rows=0 loops=1)
Index Cond: ((ci_runners.token)::text = 'R'::text)
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan using index_ci_runners_on_description_trigram (cost=0.00..222.92 rows=123 width=0) (actual time=4.701..4.701 rows=252 loops=1)
Index Cond: ((ci_runners.description)::text ~~* 'R'::text)
Buffers: shared hit=452
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_unique_ci_runner_projects_on_runner_id_and_project_id on public.ci_runner_projects (cost=0.43..1.99 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((ci_runner_projects.runner_id = ci_runners.id) AND (ci_runner_projects.project_id = 29102510))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on *SELECT* 2 (cost=0.85..77.41 rows=1 width=11) (actual time=0.106..0.107 rows=0 loops=1)
Buffers: shared hit=15
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.85..77.40 rows=1 width=3340) (actual time=0.105..0.105 rows=0 loops=1)
Buffers: shared hit=15
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runner_namespaces_on_namespace_id on public.ci_runner_namespaces (cost=0.42..28.85 rows=14 width=4) (actual time=0.104..0.104 rows=0 loops=1)
Index Cond: (ci_runner_namespaces.namespace_id = ANY ('{13132392,13132068,5849309,3730788}'::integer[]))
Buffers: shared hit=15
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_1 (cost=0.43..3.46 rows=1 width=11) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((ci_runners_1.runner_type = 3) AND (ci_runners_1.id = ci_runner_namespaces.runner_id))
Filter: (ci_runners_1.active AND (ci_runners_1.contacted_at IS NULL) AND (((ci_runners_1.token)::text = 'R'::text) OR ((ci_runners_1.description)::text ~~* 'R'::text)))
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on *SELECT* 3 (cost=0.43..91.21 rows=1 width=11) (actual time=0.002..0.003 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> Result (cost=0.43..91.20 rows=1 width=3340) (actual time=0.001..0.002 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_2 (cost=0.43..91.20 rows=1 width=11) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runners_2.runner_type = 1)
Filter: (ci_runners_2.active AND (ci_runners_2.contacted_at IS NULL) AND (((ci_runners_2.token)::text = 'R'::text) OR ((ci_runners_2.description)::text ~~* 'R'::text)))
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_taggings_on_taggable_id_and_taggable_type_and_context on public.taggings ci::runner_taggings_ec122f5 (cost=0.58..3.60 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (("ci::runner_taggings_ec122f5".taggable_id = "*SELECT* 1".id) AND (("ci::runner_taggings_ec122f5".taggable_type)::text = 'Ci::Runner'::text))
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version (cost=0.28..2.30 rows=1 width=21) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((runner_version.status = 3) AND (runner_version.version = ("*SELECT* 1".version)::text))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using tags_pkey on public.tags (cost=0.43..0.45 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (tags.id = "ci::runner_taggings_ec122f5".tag_id)
Filter: ((tags.name)::text ~~ 'shell'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.