Skip to content

GraphQL: Fix N+1 query for CiRunner.managers field

Pedro Pombeiro requested to merge pedropombeiro/415453/fix-managers-n+1-query into master

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.

Edited by Pedro Pombeiro

Merge request reports