GraphQL: Fix N+1 query for CiRunner.managers field
What does this MR do and why?
Changelog: fixed
Part of #415453 (closed)
This MR fixes an N+1 query observed with the following GraphQL query:
{
runners(first: 10) {
nodes {
id
managers {
nodes {
systemId
}
}
}
}
}
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
Old queries
Ci::RunnerManager Load (1.5ms) SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_id" = 9469 ORDER BY "ci_runner_machines"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6VNSMGT30R9H631AQY94YQQ,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'
Ci::RunnerManager Load (0.3ms) SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_id" = 9468 ORDER BY "ci_runner_machines"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6VNSMGT30R9H631AQY94YQQ,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'
Ci::RunnerManager Load (0.3ms) SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_id" = 9467 ORDER BY "ci_runner_machines"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6VNSMGT30R9H631AQY94YQQ,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'
Ci::RunnerManager Load (0.3ms) SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_id" = 9466 ORDER BY "ci_runner_machines"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6VNSMGT30R9H631AQY94YQQ,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'
Ci::RunnerManager Load (0.2ms) SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_id" = 9465 ORDER BY "ci_runner_machines"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6VNSMGT30R9H631AQY94YQQ,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'
Ci::RunnerManager Load (0.5ms) SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_id" = 9464 ORDER BY "ci_runner_machines"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6VNSMGT30R9H631AQY94YQQ,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'
Ci::RunnerManager Load (0.3ms) SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_id" = 9463 ORDER BY "ci_runner_machines"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6VNSMGT30R9H631AQY94YQQ,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'
Ci::RunnerManager Load (0.2ms) SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_id" = 9462 ORDER BY "ci_runner_machines"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6VNSMGT30R9H631AQY94YQQ,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'
Ci::RunnerManager Load (0.2ms) SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_id" = 9461 ORDER BY "ci_runner_machines"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6VNSMGT30R9H631AQY94YQQ,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'
Ci::RunnerManager Load (0.2ms) SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_id" = 9460 ORDER BY "ci_runner_machines"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6VNSMGT30R9H631AQY94YQQ,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'
New query
Ci::RunnerManager Load (0.7ms) SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_id" IN (9470, 9469, 9468, 9467, 9466, 9465, 9464, 9463, 9462, 9461) ORDER BY "ci_runner_machines"."id" DESC /*application:web,correlation_id:01H7A0F6V8D4MGVAQ4KQJ8MMMM,endpoint_id:GraphqlController#execute,db_config_name:ci,line:/app/graphql/types/ci/runner_type.rb:178:in `group_by'*/
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
Revert the production code file and run the test locally.
Database query plan
app/graphql/types/ci/runner_type.rb:178
https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/21125/commands/69056
SELECT "ci_runner_machines".*
FROM "ci_runner_machines"
WHERE "ci_runner_machines"."runner_id" IN (9470, 9469, 9468, 9467, 9466, 9465, 9464, 9463, 9462, 9461)
ORDER BY "ci_runner_machines"."id" DESC
Sort (cost=33.01..33.03 rows=10 width=99) (actual time=3.440..3.442 rows=0 loops=1)
Sort Key: ci_runner_machines.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=33 read=3
I/O Timings: read=3.328 write=0.000
-> Index Scan using index_ci_runner_machines_on_runner_id_and_system_xid on public.ci_runner_machines (cost=0.42..32.84 rows=10 width=99) (actual time=3.418..3.419 rows=0 loops=1)
Index Cond: (ci_runner_machines.runner_id = ANY ('{9470,9469,9468,9467,9466,9465,9464,9463,9462,9461}'::bigint[]))
Buffers: shared hit=30 read=3
I/O Timings: read=3.328 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.
Edited by Pedro Pombeiro