GraphQL: Fix N+1 issue in RunnerOwnerProjectResolver
What does this MR do and why?
Describe in detail what your merge request does and why.
This MR adds support for preloading relationships for fullPath and webUrl properties, which are in use for the Runners' admin UI.
Part of #384066 (closed) and #385355 (closed)
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
-
Run the following query:
{ runners(type: PROJECT_TYPE, last: 20) { count nodes { id runnerType ownerProject { id fullPath webUrl } } } }
The queries generated by this branch should no longer generate a route query per-runner.
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.
Database queries
Old queries
Processing by GraphqlController#execute as */*
Parameters: {"query"=>"{\n runners(type: PROJECT_TYPE, last: 20) {\n count\n nodes {\n id\n runnerType\n ownerProject {\n id\n fullPath\n webUrl\n }\n }\n }\n}\n", "variables"=>"[FILTERED]", "graphql"=>{"query"=>"{\n runners(type: PROJECT_TYPE, last: 20) {\n count\n nodes {\n id\n runnerType\n ownerProject {\n id\n fullPath\n webUrl\n }\n }\n }\n}\n", "variables"=>"[FILTERED]"}}
(0.3ms) SELECT COUNT(*) FROM "ci_runners" WHERE "ci_runners"."runner_type" = 3 /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:graphql:unknown,db_config_name:ci,line:/app/graphql/types/countable_connection_type.rb:20:in `count'*/
↳ app/graphql/types/countable_connection_type.rb:20:in `count'
Ci::Runner Load (0.2ms) SELECT "ci_runners".* FROM "ci_runners" WHERE "ci_runners"."runner_type" = 3 ORDER BY "ci_runners"."created_at" ASC, "ci_runners"."id" ASC LIMIT 21 /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:114:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:114:in `block in limited_nodes'
Ci::RunnerProject Load (0.2ms) SELECT "id", "runner_id", "project_id" FROM (SELECT id, runner_id, project_id, ROW_NUMBER() OVER (PARTITION BY runner_id ORDER BY id ASC) FROM "ci_runner_projects" WHERE "ci_runner_projects"."runner_id" IN (97978, 97977, 1227, 1226, 1225, 1224, 1223, 1222, 1221, 1220)) temp WHERE row_number = 1 /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:ci,line:/app/graphql/resolvers/ci/runner_owner_project_resolver.rb:47:in `group_by'*/
↳ app/graphql/resolvers/ci/runner_owner_project_resolver.rb:47:in `group_by'
Project Load (0.4ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (20, 23) /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_policy_preloader.rb:13:in `execute'*/
↳ app/models/preloaders/project_policy_preloader.rb:13:in `execute'
Group Load (0.3ms) SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (22, 80) /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_policy_preloader.rb:13:in `execute'*/
↳ app/models/preloaders/project_policy_preloader.rb:13:in `execute'
Route Load (0.2ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" IN (22, 80) /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_policy_preloader.rb:13:in `execute'*/
↳ app/models/preloaders/project_policy_preloader.rb:13:in `execute'
Namespace Load (0.2ms) SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."id" IN (22, 80) /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_policy_preloader.rb:13:in `execute'*/
↳ app/models/preloaders/project_policy_preloader.rb:13:in `execute'
Project Load (0.5ms) SELECT "projects".*, "project_authorizations"."user_id", "project_authorizations"."access_level" FROM "projects" LEFT OUTER JOIN "project_authorizations" AS project_authorizations ON "project_authorizations"."project_id" = "projects"."id" AND "project_authorizations"."user_id" = 1 WHERE "projects"."id" IN (20, 23) /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/user_max_access_level_in_projects_preloader.rb:34:in `execute'*/
↳ app/models/preloaders/user_max_access_level_in_projects_preloader.rb:34:in `execute'
Namespace Load (0.7ms) SELECT namespaces.*, root_query.id as source_id FROM "namespaces" INNER JOIN (SELECT projects.id, namespaces.traversal_ids[1] as root_id FROM "projects" INNER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "projects"."id" IN (20, 23)) as root_query ON root_query.root_id = namespaces.id /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'*/
↳ app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'
IpRestriction Load (0.2ms) SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" IN (22, 79) /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'*/
↳ app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'
SamlProvider Load (0.1ms) SELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" IN (22, 79) /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'*/
↳ app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'
Route Load (0.2ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 23 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/concerns/routable.rb:121:in `full_path'*/
↳ app/models/concerns/routable.rb:121:in `full_path'
Route Load (0.2ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 80 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/concerns/routable.rb:121:in `full_path'*/
↳ app/models/concerns/routable.rb:121:in `full_path'
Route Load (0.2ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 20 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/concerns/routable.rb:121:in `full_path'*/
↳ app/models/concerns/routable.rb:121:in `full_path'
Route Load (0.2ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 22 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01GM3GGPVG7JJDXD8JHYP21YSC,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/concerns/routable.rb:121:in `full_path'*/
↳ app/models/concerns/routable.rb:121:in `full_path'
Completed 200 OK in 70ms (Views: 0.2ms | ActiveRecord: 4.2ms | Elasticsearch: 0.0ms | Allocations: 88998)New queries
Processing by GraphqlController#execute as */*
Parameters: {"query"=>"{\n runners(type: PROJECT_TYPE, last: 20) {\n count\n nodes {\n id\n runnerType\n ownerProject {\n id\n fullPath\n webUrl\n }\n }\n }\n}\n", "variables"=>"[FILTERED]", "graphql"=>{"query"=>"{\n runners(type: PROJECT_TYPE, last: 20) {\n count\n nodes {\n id\n runnerType\n ownerProject {\n id\n fullPath\n webUrl\n }\n }\n }\n}\n", "variables"=>"[FILTERED]"}}
(0.3ms) SELECT COUNT(*) FROM "ci_runners" WHERE "ci_runners"."runner_type" = 3 /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:graphql:unknown,db_config_name:ci,line:/app/graphql/types/countable_connection_type.rb:20:in `count'*/
↳ app/graphql/types/countable_connection_type.rb:20:in `count'
Ci::Runner Load (0.4ms) SELECT "ci_runners".* FROM "ci_runners" WHERE "ci_runners"."runner_type" = 3 ORDER BY "ci_runners"."created_at" ASC, "ci_runners"."id" ASC LIMIT 21 /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:114:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:114:in `block in limited_nodes'
Ci::RunnerProject Load (0.3ms) SELECT "id", "runner_id", "project_id" FROM (SELECT id, runner_id, project_id, ROW_NUMBER() OVER (PARTITION BY runner_id ORDER BY id ASC) FROM "ci_runner_projects" WHERE "ci_runner_projects"."runner_id" IN (97978, 97977, 1227, 1226, 1225, 1224, 1223, 1222, 1221, 1220)) temp WHERE row_number = 1 /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:GraphqlController#execute,db_config_name:ci,line:/app/graphql/resolvers/ci/runner_owner_project_resolver.rb:49:in `group_by'*/
↳ app/graphql/resolvers/ci/runner_owner_project_resolver.rb:49:in `group_by'
Project Load (0.7ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (20, 23) /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_policy_preloader.rb:13:in `execute'*/
↳ app/models/preloaders/project_policy_preloader.rb:13:in `execute'
ProjectFeature Load (0.3ms) SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" IN (20, 23) /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_policy_preloader.rb:13:in `execute'*/
↳ app/models/preloaders/project_policy_preloader.rb:13:in `execute'
Route Load (0.3ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" IN (20, 23) /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_policy_preloader.rb:13:in `execute'*/
↳ app/models/preloaders/project_policy_preloader.rb:13:in `execute'
Group Load (0.4ms) SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (22, 80) /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_policy_preloader.rb:13:in `execute'*/
↳ app/models/preloaders/project_policy_preloader.rb:13:in `execute'
Route Load (0.3ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" IN (22, 80) /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_policy_preloader.rb:13:in `execute'*/
↳ app/models/preloaders/project_policy_preloader.rb:13:in `execute'
Namespace Load (0.8ms) SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."id" IN (22, 80) /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_policy_preloader.rb:13:in `execute'*/
↳ app/models/preloaders/project_policy_preloader.rb:13:in `execute'
CACHE Route Load (0.0ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" IN (22, 80)
↳ app/models/preloaders/project_policy_preloader.rb:13:in `execute'
Project Load (0.9ms) SELECT "projects".*, "project_authorizations"."user_id", "project_authorizations"."access_level" FROM "projects" LEFT OUTER JOIN "project_authorizations" AS project_authorizations ON "project_authorizations"."project_id" = "projects"."id" AND "project_authorizations"."user_id" = 1 WHERE "projects"."id" IN (20, 23) /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/user_max_access_level_in_projects_preloader.rb:34:in `execute'*/
↳ app/models/preloaders/user_max_access_level_in_projects_preloader.rb:34:in `execute'
Namespace Load (0.9ms) SELECT namespaces.*, root_query.id as source_id FROM "namespaces" INNER JOIN (SELECT projects.id, namespaces.traversal_ids[1] as root_id FROM "projects" INNER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "projects"."id" IN (20, 23)) as root_query ON root_query.root_id = namespaces.id /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'*/
↳ app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'
IpRestriction Load (0.2ms) SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" IN (22, 79) /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'*/
↳ app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'
SamlProvider Load (0.6ms) SELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" IN (22, 79) /*application:web,correlation_id:01GM3GFCYFJD458FZZ4RPPY46N,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'*/
↳ app/models/preloaders/project_root_ancestor_preloader.rb:20:in `group_by'
Completed 200 OK in 86ms (Views: 0.2ms | ActiveRecord: 6.3ms | Elasticsearch: 0.0ms | Allocations: 82375)Edited by Pedro Pombeiro