GraphQL: Add count property to RunnerType’s connection
What does this MR do and why?
Describe in detail what your merge request does and why.
This MR adds a count property to runners queries. Specifically it:
- changes the connection type class for
RunnerTypeto beTypes::CountableConnectionType; - Adds a database index on
ci_runners.activeso that queries onactive = TRUEcan be performant. Currently we only have a partial indicesindex_ci_runners_on_contacted_at_and_id_where_inactive/index_ci_runners_on_created_at_and_id_where_inactive.
Screenshots or screen recordings
These are strongly recommended to assist reviewers and reduce the time to merge your change.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
-
Enter the following query:
query getRunners { runners(active:true) { count nodes { id description shortSha contactedAt active } } }
The resulting JSON should contain the runner count under data/runners node.
Database query plans
Scenario 1: Count active runners with a given tag
GraphQL query
query getRunners {
runners(tagList:["shell"], active: true) {
count
}
}SQL query
SELECT
COUNT(*)
FROM
"ci_runners"
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"."active" = TRUE
LIMIT 100Query plan (before)
Details: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7853/commands/28066
Limit (cost=1409.29..1409.30 rows=1 width=8) (actual time=17750.875..17750.879 rows=1 loops=1)
Buffers: shared hit=188111 read=15534 dirtied=432
I/O Timings: read=15722.639 write=0.000
-> Aggregate (cost=1409.29..1409.30 rows=1 width=8) (actual time=17750.872..17750.875 rows=1 loops=1)
Buffers: shared hit=188111 read=15534 dirtied=432
I/O Timings: read=15722.639 write=0.000
-> Nested Loop (cost=1.55..1409.28 rows=1 width=0) (actual time=5.394..17734.331 rows=25101 loops=1)
Buffers: shared hit=188111 read=15534 dirtied=432
I/O Timings: read=15722.639 write=0.000
-> Nested Loop (cost=1.12..1406.61 rows=5 width=8) (actual time=0.090..1579.139 rows=26014 loops=1)
Buffers: shared hit=99504
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_tags_on_name on public.tags (cost=0.42..3.45 rows=1 width=4) (actual time=0.050..0.086 rows=1 loops=1)
Index Cond: ((tags.name)::text = 'shell'::text)
Filter: ((tags.name)::text ~~ 'shell'::text)
Rows Removed by Filter: 0
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using taggings_idx on public.taggings ci::runner_taggings_ec122f5 (cost=0.70..1401.19 rows=197 width=12) (actual time=0.038..1569.832 rows=26014 loops=1)
Index Cond: (("ci::runner_taggings_ec122f5".tag_id = tags.id) AND ("ci::runner_taggings_ec122f5".taggable_type = 'Ci::Runner'::text))
Heap Fetches: 296
Buffers: shared hit=99500
I/O Timings: read=0.000 write=0.000
-> Index Scan using ci_runners_pkey on public.ci_runners (cost=0.43..0.54 rows=1 width=4) (actual time=0.619..0.619 rows=1 loops=26014)
Index Cond: (ci_runners.id = "ci::runner_taggings_ec122f5".taggable_id)
Filter: ci_runners.active
Rows Removed by Filter: 0
Buffers: shared hit=88607 read=15534 dirtied=432
I/O Timings: read=15722.639 write=0.000Query plan (after)
Details: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7866/commands/28097
Limit (cost=1413.23..1413.24 rows=1 width=8) (actual time=3382.406..3382.410 rows=1 loops=1)
Buffers: shared hit=87152 read=100542
I/O Timings: read=2187.718 write=0.000
-> Aggregate (cost=1413.23..1413.24 rows=1 width=8) (actual time=3382.405..3382.408 rows=1 loops=1)
Buffers: shared hit=87152 read=100542
I/O Timings: read=2187.718 write=0.000
-> Nested Loop (cost=1.55..1413.23 rows=1 width=0) (actual time=0.525..3379.295 rows=25237 loops=1)
Buffers: shared hit=87152 read=100542
I/O Timings: read=2187.718 write=0.000
-> Nested Loop (cost=1.12..1410.95 rows=5 width=8) (actual time=0.406..3210.391 rows=26150 loops=1)
Buffers: shared hit=4056 read=95835
I/O Timings: read=2120.405 write=0.000
-> Index Scan using index_tags_on_name on public.tags (cost=0.42..3.45 rows=1 width=4) (actual time=0.025..0.028 rows=1 loops=1)
Index Cond: ((tags.name)::text = 'shell'::text)
Filter: ((tags.name)::text ~~ 'shell'::text)
Rows Removed by Filter: 0
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using taggings_idx on public.taggings ci::runner_taggings_ec122f5 (cost=0.70..1405.53 rows=197 width=12) (actual time=0.378..3206.214 rows=26150 loops=1)
Index Cond: (("ci::runner_taggings_ec122f5".tag_id = tags.id) AND ("ci::runner_taggings_ec122f5".taggable_type = 'Ci::Runner'::text))
Heap Fetches: 450
Buffers: shared hit=4052 read=95835
I/O Timings: read=2120.405 write=0.000
-> Index Only Scan using index_ci_runners_on_active on public.ci_runners (cost=0.43..0.46 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=26150)
Index Cond: ((ci_runners.active = true) AND (ci_runners.id = "ci::runner_taggings_ec122f5".taggable_id))
Heap Fetches: 1747
Buffers: shared hit=83096 read=4707
I/O Timings: read=67.312 write=0.000
Scenario 2: Group runners page query for Status = Active
NOTE: This is run against the namespace with the most runners in the production database
SQL query
SELECT
"ci_runners".*
FROM ((
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 (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{7595039}')))))
UNION (
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" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{7595039}')))))) ci_runners
WHERE
"ci_runners"."active" = TRUE
ORDER BY
"ci_runners"."created_at" DESC,
"ci_runners"."id" DESC
LIMIT 20Query plan (before)
Details: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7853/commands/28067
Limit (cost=476.63..476.63 rows=3 width=3767) (actual time=5261.111..5261.212 rows=20 loops=1)
Buffers: shared hit=2965978
I/O Timings: read=0.000 write=0.000
-> Sort (cost=476.63..476.63 rows=3 width=3767) (actual time=5261.109..5261.196 rows=20 loops=1)
Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
Sort Method: top-N heapsort Memory: 34kB
Buffers: shared hit=2965978
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=476.54..476.57 rows=3 width=3767) (actual time=4487.699..4991.714 rows=596897 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.version, ci_runners.revision, ci_runners.platform, ci_runners.architecture, ci_runners.run_untagged, ci_runners.locked, ci_runners.access_level, ci_runners.ip_address, 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.config, ci_runners.executor_type
Buffers: shared hit=2965972
I/O Timings: read=0.000 write=0.000
-> Append (cost=212.57..476.37 rows=3 width=3767) (actual time=3.824..3274.382 rows=596897 loops=1)
Buffers: shared hit=2965972
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=212.57..220.55 rows=1 width=234) (actual time=3.824..3160.251 rows=596897 loops=1)
Buffers: shared hit=2965685
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=212.14..219.90 rows=1 width=4) (actual time=3.805..693.800 rows=596897 loops=1)
Buffers: shared hit=576738
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=211.71..211.74 rows=3 width=8) (actual time=3.778..3.863 rows=8 loops=1)
Group Key: namespaces.id
Buffers: shared hit=145
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=155.09..211.70 rows=3 width=8) (actual time=3.665..3.809 rows=8 loops=1)
Buffers: shared hit=145
I/O Timings: read=0.000 write=0.000
-> Bitmap Heap Scan on public.namespaces namespaces_1 (cost=154.53..177.50 rows=15 width=4) (actual time=3.618..3.652 rows=8 loops=1)
Buffers: shared hit=109
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan using index_namespaces_on_traversal_ids_for_groups (cost=0.00..154.52 rows=15 width=0) (actual time=3.608..3.615 rows=8 loops=1)
Index Cond: (namespaces_1.traversal_ids @> '{7595039}'::integer[])
Buffers: shared hit=102
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_namespaces_on_type_and_id on public.namespaces (cost=0.56..2.28 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=8)
Index Cond: ((namespaces.type = 'Group'::text) AND (namespaces.id = namespaces_1.id))
Heap Fetches: 1
Buffers: shared hit=36
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.43..1.89 rows=83 width=8) (actual time=0.009..69.847 rows=74612 loops=8)
Index Cond: (ci_runner_namespaces.namespace_id = namespaces.id)
Buffers: shared hit=576593
I/O Timings: read=0.000 write=0.000
-> Index Scan using ci_runners_pkey on public.ci_runners (cost=0.43..0.65 rows=1 width=234) (actual time=0.003..0.003 rows=1 loops=596897)
Index Cond: (ci_runners.id = ci_runner_namespaces.runner_id)
Filter: ci_runners.active
Rows Removed by Filter: 0
Buffers: shared hit=2388947
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=227.96..255.78 rows=2 width=234) (actual time=4.302..4.313 rows=0 loops=1)
Buffers: shared hit=287
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=227.53..254.58 rows=2 width=4) (actual time=4.279..4.282 rows=0 loops=1)
Buffers: shared hit=287
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=227.11..227.36 rows=25 width=4) (actual time=4.008..4.019 rows=37 loops=1)
Group Key: projects.id
Buffers: shared hit=176
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=155.09..227.04 rows=25 width=4) (actual time=3.824..3.941 rows=37 loops=1)
Buffers: shared hit=176
I/O Timings: read=0.000 write=0.000
-> Bitmap Heap Scan on public.namespaces namespaces_2 (cost=154.53..177.50 rows=15 width=4) (actual time=3.675..3.691 rows=8 loops=1)
Buffers: shared hit=109
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan using index_namespaces_on_traversal_ids_for_groups (cost=0.00..154.52 rows=15 width=0) (actual time=3.638..3.639 rows=8 loops=1)
Index Cond: (namespaces_2.traversal_ids @> '{7595039}'::integer[])
Buffers: shared hit=102
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.56..3.09 rows=21 width=8) (actual time=0.020..0.024 rows=5 loops=8)
Index Cond: (projects.namespace_id = namespaces_2.id)
Heap Fetches: 2
Buffers: shared hit=67
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runner_projects_on_project_id on public.ci_runner_projects (cost=0.43..0.85 rows=24 width=8) (actual time=0.005..0.005 rows=0 loops=37)
Index Cond: (ci_runner_projects.project_id = projects.id)
Buffers: shared hit=111
I/O Timings: read=0.000 write=0.000
-> Index Scan using ci_runners_pkey on public.ci_runners ci_runners_1 (cost=0.43..0.60 rows=1 width=234) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runners_1.id = ci_runner_projects.runner_id)
Filter: ci_runners_1.active
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000Query plan (after)
Details: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7853/commands/28070
Limit (cost=476.57..476.57 rows=3 width=3767) (actual time=5893.565..5893.586 rows=20 loops=1)
Buffers: shared hit=2962122 read=2497
I/O Timings: read=102.751 write=0.000
-> Sort (cost=476.57..476.57 rows=3 width=3767) (actual time=5893.562..5893.579 rows=20 loops=1)
Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
Sort Method: top-N heapsort Memory: 34kB
Buffers: shared hit=2962122 read=2497
I/O Timings: read=102.751 write=0.000
-> HashAggregate (cost=476.48..476.51 rows=3 width=3767) (actual time=5125.978..5616.763 rows=596897 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.version, ci_runners.revision, ci_runners.platform, ci_runners.architecture, ci_runners.run_untagged, ci_runners.locked, ci_runners.access_level, ci_runners.ip_address, 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.config, ci_runners.executor_type
Buffers: shared hit=2962116 read=2497
I/O Timings: read=102.751 write=0.000
-> Append (cost=212.57..476.31 rows=3 width=3767) (actual time=3.862..3918.723 rows=596897 loops=1)
Buffers: shared hit=2962116 read=2497
I/O Timings: read=102.751 write=0.000
-> Nested Loop (cost=212.57..220.52 rows=1 width=234) (actual time=3.861..3806.863 rows=596897 loops=1)
Buffers: shared hit=2961829 read=2497
I/O Timings: read=102.751 write=0.000
-> Nested Loop (cost=212.14..219.90 rows=1 width=4) (actual time=3.587..686.523 rows=596897 loops=1)
Buffers: shared hit=576738
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=211.71..211.74 rows=3 width=8) (actual time=3.559..3.569 rows=8 loops=1)
Group Key: namespaces.id
Buffers: shared hit=145
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=155.09..211.70 rows=3 width=8) (actual time=3.448..3.552 rows=8 loops=1)
Buffers: shared hit=145
I/O Timings: read=0.000 write=0.000
-> Bitmap Heap Scan on public.namespaces namespaces_1 (cost=154.53..177.50 rows=15 width=4) (actual time=3.405..3.426 rows=8 loops=1)
Buffers: shared hit=109
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan using index_namespaces_on_traversal_ids_for_groups (cost=0.00..154.52 rows=15 width=0) (actual time=3.393..3.394 rows=8 loops=1)
Index Cond: (namespaces_1.traversal_ids @> '{7595039}'::integer[])
Buffers: shared hit=102
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_namespaces_on_type_and_id on public.namespaces (cost=0.56..2.28 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=8)
Index Cond: ((namespaces.type = 'Group'::text) AND (namespaces.id = namespaces_1.id))
Heap Fetches: 1
Buffers: shared hit=36
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.43..1.89 rows=83 width=8) (actual time=0.010..69.359 rows=74612 loops=8)
Index Cond: (ci_runner_namespaces.namespace_id = namespaces.id)
Buffers: shared hit=576593
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runners_on_active on public.ci_runners (cost=0.43..0.63 rows=1 width=234) (actual time=0.005..0.005 rows=1 loops=596897)
Index Cond: ((ci_runners.active = true) AND (ci_runners.id = ci_runner_namespaces.runner_id))
Buffers: shared hit=2385091 read=2497
I/O Timings: read=102.751 write=0.000
-> Nested Loop (cost=227.96..255.74 rows=2 width=234) (actual time=3.592..3.598 rows=0 loops=1)
Buffers: shared hit=287
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=227.53..254.58 rows=2 width=4) (actual time=3.592..3.596 rows=0 loops=1)
Buffers: shared hit=287
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=227.11..227.36 rows=25 width=4) (actual time=3.429..3.439 rows=37 loops=1)
Group Key: projects.id
Buffers: shared hit=176
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=155.09..227.04 rows=25 width=4) (actual time=3.298..3.410 rows=37 loops=1)
Buffers: shared hit=176
I/O Timings: read=0.000 write=0.000
-> Bitmap Heap Scan on public.namespaces namespaces_2 (cost=154.53..177.50 rows=15 width=4) (actual time=3.266..3.281 rows=8 loops=1)
Buffers: shared hit=109
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan using index_namespaces_on_traversal_ids_for_groups (cost=0.00..154.52 rows=15 width=0) (actual time=3.256..3.256 rows=8 loops=1)
Index Cond: (namespaces_2.traversal_ids @> '{7595039}'::integer[])
Buffers: shared hit=102
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.56..3.09 rows=21 width=8) (actual time=0.010..0.014 rows=5 loops=8)
Index Cond: (projects.namespace_id = namespaces_2.id)
Heap Fetches: 2
Buffers: shared hit=67
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runner_projects_on_project_id on public.ci_runner_projects (cost=0.43..0.85 rows=24 width=8) (actual time=0.004..0.004 rows=0 loops=37)
Index Cond: (ci_runner_projects.project_id = projects.id)
Buffers: shared hit=111
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runners_on_active on public.ci_runners ci_runners_1 (cost=0.43..0.58 rows=1 width=234) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((ci_runners_1.active = true) AND (ci_runners_1.id = ci_runner_projects.runner_id))
I/O Timings: read=0.000 write=0.000MR 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.
Closes #333806 (closed)
Edited by Pedro Pombeiro