Add runner filter for generic owner field
-
Please check this box if this contribution uses AI-generated content as outlined in the GitLab DCO & CLA
What does this MR do and why?
This MR has back-end changes to add a runners finder with a generic owner field intended to search for the owning Project/Group, or for Administrator Instance runners.
See the relevant design here: #378558. The screenshots in this design give an idea of how this back-end functionality is intended to be used.
I took this approach so that a filter text passed to the back-end can be added to the existing GraphQL calls on the front-end to filter by these different fields.
How to set up and validate locally
Make sure you have at least one GitLab runner registered and running.
-
Use the following query:
{ runners(owner: "Test") { nodes { id description } } } -
The query will return any project runners with project matching the name "Test", as well as group runners with a group matching the name "Test"
-
The input "Administrator" returns all Administrator Instance runners
{ runners(ownerWildcard: ADMINISTRATOR) { nodes { id description } } } -
The input "None" should show all non-Admin Instance runners, and orphaned Project/Group runners with no owning Project/Group
{ runners(ownerWildcard: NONE) { nodes { id runnerType groups { nodes { id } } projects { count } } } }
Database query plans
Getting a page's worth of runners filtered by ownerWildcard: NONE (common scenario)
https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/32453/commands/100210
SELECT *
FROM ((
SELECT "ci_runners".*
FROM "ci_runners"
LEFT OUTER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
WHERE "ci_runners"."runner_type" = 2
AND "ci_runner_namespaces"."namespace_id" IS NULL)
UNION (
SELECT "ci_runners".*
FROM "ci_runners"
LEFT OUTER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
WHERE "ci_runners"."runner_type" = 3
AND "ci_runner_projects"."project_id" IS NULL)
UNION (
SELECT "ci_runners".*
FROM "ci_runners"
WHERE "ci_runners"."runner_type" = 1
AND "ci_runners"."creator_id" IS NULL)) ci_runners
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 21 Limit (cost=1335562.76..1335562.81 rows=21 width=1761) (actual time=8535.308..8542.758 rows=21 loops=1)
Buffers: shared hit=1644773 read=346401 written=4882
I/O Timings: read=5966.104 write=118.820
-> Sort (cost=1335562.76..1335563.00 rows=98 width=1761) (actual time=8535.300..8542.747 rows=21 loops=1)
Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
Sort Method: top-N heapsort Memory: 37kB
Buffers: shared hit=1644773 read=346401 written=4882
I/O Timings: read=5966.104 write=118.820
-> HashAggregate (cost=1335558.16..1335559.14 rows=98 width=1761) (actual time=6196.292..8053.603 rows=1043394 loops=1)
Group Key: ci_runners.id, ci_runners.token, ci_runners.created_at, ci_runners.updated_at, ci_runners.description, ci_runners.contacted_at, ci_runners.active, ci_runners.name, ci_runners.run_untagged, ci_runners.locked, ci_runners.access_level, ci_runners.maximum_timeout, ci_runners.runner_type, ci_runners.token_encrypted, ci_runners.public_projects_minutes_cost_factor, ci_runners.private_projects_minutes_cost_factor, ci_runners.maintainer_note, ci_runners.token_expires_at, ci_runners.allowed_plans, ci_runners.registration_type, ci_runners.creator_id, ci_runners.creation_state, ci_runners.allowed_plan_ids, ci_runners.sharding_key_id
Buffers: shared hit=1644770 read=346401 written=4882
I/O Timings: read=5966.104 write=118.820
-> Append (cost=41665.31..1335552.28 rows=98 width=1761) (actual time=211.148..4538.124 rows=1043394 loops=1)
Buffers: shared hit=1644770 read=346401 written=4882
I/O Timings: read=5966.104 write=118.820
-> Gather (cost=41665.31..603048.96 rows=1 width=246) (actual time=211.145..2448.270 rows=35998 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=528988 read=143898 written=4246
I/O Timings: read=2836.997 write=100.193
-> Parallel Hash Left Join (cost=40665.31..602048.86 rows=1 width=246) (actual time=206.334..2466.966 rows=11999 loops=3)
Hash Cond: (ci_runners.id = ci_runner_namespaces.runner_id)
Filter: (ci_runner_namespaces.namespace_id IS NULL)
Rows Removed by Filter: 258097
Buffers: shared hit=528988 read=143898 written=4246
I/O Timings: read=2836.997 write=100.193
-> Parallel Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners (cost=0.43..559622.16 rows=361247 width=246) (actual time=0.275..2015.893 rows=270096 loops=3)
Index Cond: (ci_runners.runner_type = 2)
Buffers: shared hit=528923 read=135547 written=4246
I/O Timings: read=2646.916 write=100.193
-> Parallel Hash (cost=36633.28..36633.28 rows=322528 width=8) (actual time=202.608..202.641 rows=258097 loops=3)
Buckets: 1048576 Batches: 1 Memory Usage: 38528kB
Buffers: shared hit=1 read=8351
I/O Timings: read=190.081 write=0.000
-> Parallel Seq Scan on public.ci_runner_namespaces (cost=0.00..36633.28 rows=322528 width=8) (actual time=0.106..100.535 rows=258097 loops=3)
Buffers: shared hit=1 read=8351
I/O Timings: read=190.081 write=0.000
-> Gather (cost=1001.23..732348.57 rows=1 width=246) (actual time=1.187..2000.516 rows=1007334 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1115715 read=202454 written=636
I/O Timings: read=3128.346 write=18.627
-> Merge Left Join (cost=1.23..731348.47 rows=1 width=246) (actual time=0.344..2695.951 rows=335778 loops=3)
Merge Cond: (ci_runners_1.id = ci_runner_projects.runner_id)
Filter: (ci_runner_projects.project_id IS NULL)
Rows Removed by Filter: 396902
Buffers: shared hit=1115715 read=202454 written=636
I/O Timings: read=3128.346 write=18.627
-> Parallel Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_1 (cost=0.43..666762.38 rows=831100 width=246) (actual time=0.146..2017.465 rows=622218 loops=3)
Index Cond: (ci_runners_1.runner_type = 3)
Buffers: shared hit=1019029 read=186166 written=613
I/O Timings: read=2846.732 write=17.811
-> Index Only Scan using index_unique_ci_runner_projects_on_runner_id_and_project_id on public.ci_runner_projects (cost=0.43..52437.41 rows=1863545 width=8) (actual time=0.103..424.011 rows=1737196 loops=3)
Heap Fetches: 74937
Buffers: shared hit=96686 read=16288 written=23
I/O Timings: read=281.615 write=0.816
-> Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_2 (cost=0.43..153.28 rows=96 width=246) (actual time=0.160..1.221 rows=62 loops=1)
Index Cond: (ci_runners_2.runner_type = 1)
Filter: (ci_runners_2.creator_id IS NULL)
Rows Removed by Filter: 50
Buffers: shared hit=67 read=49
I/O Timings: read=0.761 write=0.000Getting a count of orphaned runners in the instance (not common, only accessible directly in GraphQL API)
https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/32453/commands/100206
SELECT COUNT(*)
FROM ((
SELECT "ci_runners".*
FROM "ci_runners"
LEFT OUTER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
WHERE "ci_runners"."runner_type" = 2
AND "ci_runner_namespaces"."namespace_id" IS NULL)
UNION (
SELECT "ci_runners".*
FROM "ci_runners"
LEFT OUTER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
WHERE "ci_runners"."runner_type" = 3
AND "ci_runner_projects"."project_id" IS NULL)
UNION (
SELECT "ci_runners".*
FROM "ci_runners"
WHERE "ci_runners"."runner_type" = 1
AND "ci_runners"."creator_id" IS NULL)) ci_runners Aggregate (cost=1335560.36..1335560.37 rows=1 width=8) (actual time=15378.283..15386.819 rows=1 loops=1)
Buffers: shared hit=1604597 read=395357 dirtied=52026 written=28590
I/O Timings: read=24339.317 write=665.432
-> HashAggregate (cost=1335558.16..1335559.14 rows=98 width=1761) (actual time=13767.875..15333.813 rows=1043394 loops=1)
Group Key: ci_runners.id, ci_runners.token, ci_runners.created_at, ci_runners.updated_at, ci_runners.description, ci_runners.contacted_at, ci_runners.active, ci_runners.name, ci_runners.run_untagged, ci_runners.locked, ci_runners.access_level, ci_runners.maximum_timeout, ci_runners.runner_type, ci_runners.token_encrypted, ci_runners.public_projects_minutes_cost_factor, ci_runners.private_projects_minutes_cost_factor, ci_runners.maintainer_note, ci_runners.token_expires_at, ci_runners.allowed_plans, ci_runners.registration_type, ci_runners.creator_id, ci_runners.creation_state, ci_runners.allowed_plan_ids, ci_runners.sharding_key_id
Buffers: shared hit=1604597 read=395357 dirtied=52026 written=28590
I/O Timings: read=24339.317 write=665.432
-> Append (cost=41665.31..1335552.28 rows=98 width=1761) (actual time=325.369..12145.682 rows=1043394 loops=1)
Buffers: shared hit=1604597 read=395357 dirtied=52026 written=28590
I/O Timings: read=24339.317 write=665.432
-> Gather (cost=41665.31..603048.96 rows=1 width=246) (actual time=325.367..7302.669 rows=35998 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=486636 read=189772 dirtied=20827 written=6492
I/O Timings: read=14667.118 write=140.564
-> Parallel Hash Left Join (cost=40665.31..602048.86 rows=1 width=246) (actual time=322.036..7327.004 rows=11999 loops=3)
Hash Cond: (ci_runners.id = ci_runner_namespaces.runner_id)
Filter: (ci_runner_namespaces.namespace_id IS NULL)
Rows Removed by Filter: 258097
Buffers: shared hit=486636 read=189772 dirtied=20827 written=6492
I/O Timings: read=14667.118 write=140.564
-> Parallel Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners (cost=0.43..559622.16 rows=361247 width=246) (actual time=18.138..6741.209 rows=270096 loops=3)
Index Cond: (ci_runners.runner_type = 2)
Buffers: shared hit=486571 read=181421 dirtied=19112 written=6492
I/O Timings: read=14252.624 write=140.564
-> Parallel Hash (cost=36633.28..36633.28 rows=322528 width=8) (actual time=288.115..288.118 rows=258097 loops=3)
Buckets: 1048576 Batches: 1 Memory Usage: 38560kB
Buffers: shared hit=1 read=8351 dirtied=1715
I/O Timings: read=414.494 write=0.000
-> Parallel Seq Scan on public.ci_runner_namespaces (cost=0.00..36633.28 rows=322528 width=8) (actual time=1.135..186.214 rows=258097 loops=3)
Buffers: shared hit=1 read=8351 dirtied=1715
I/O Timings: read=414.494 write=0.000
-> Gather (cost=1001.23..732348.57 rows=1 width=246) (actual time=1.321..4753.662 rows=1007334 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1117895 read=205533 dirtied=31183 written=22091
I/O Timings: read=9671.557 write=524.734
-> Merge Left Join (cost=1.23..731348.47 rows=1 width=246) (actual time=0.401..5494.377 rows=335778 loops=3)
Merge Cond: (ci_runners_1.id = ci_runner_projects.runner_id)
Filter: (ci_runner_projects.project_id IS NULL)
Rows Removed by Filter: 396902
Buffers: shared hit=1117895 read=205533 dirtied=31183 written=22091
I/O Timings: read=9671.557 write=524.734
-> Parallel Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_1 (cost=0.43..666762.38 rows=831100 width=246) (actual time=0.169..4798.246 rows=622218 loops=3)
Index Cond: (ci_runners_1.runner_type = 3)
Buffers: shared hit=1021111 read=189080 dirtied=30982 written=19703
I/O Timings: read=9375.171 write=465.479
-> Index Only Scan using index_unique_ci_runner_projects_on_runner_id_and_project_id on public.ci_runner_projects (cost=0.43..52437.41 rows=1863545 width=8) (actual time=0.119..445.862 rows=1737196 loops=3)
Heap Fetches: 75216
Buffers: shared hit=96784 read=16453 dirtied=201 written=2388
I/O Timings: read=296.387 write=59.255
-> Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_2 (cost=0.43..153.28 rows=96 width=246) (actual time=0.108..1.284 rows=62 loops=1)
Index Cond: (ci_runners_2.runner_type = 1)
Filter: (ci_runners_2.creator_id IS NULL)
Rows Removed by Filter: 50
Buffers: shared hit=66 read=52 dirtied=16 written=7
I/O Timings: read=0.642 write=0.134Getting a count of users owned by Administrators (not common, only accessible directly in GraphQL API)
https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/32453/commands/100208
SELECT COUNT(DISTINCT "ci_runners"."id")
FROM "ci_runners"
WHERE "ci_runners"."runner_type" = 1
AND "ci_runners"."creator_id" IN (...) Aggregate (cost=154.63..154.64 rows=1 width=8) (actual time=1.004..1.005 rows=1 loops=1)
Buffers: shared hit=128
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=1.26..154.63 rows=1 width=4) (actual time=0.546..0.948 rows=50 loops=1)
Index Cond: (ci_runners.runner_type = 1)
Filter: (ci_runners.creator_id = ANY ('{...}'::bigint[]))
Rows Removed by Filter: 62
Buffers: shared hit=119
I/O Timings: read=0.000 write=0.000Edited by Pedro Pombeiro