Skip to content

GraphQL - Preload epics group using Lookahead

Eugenia Grieff requested to merge 210553-improve-performance-of-graphql-groupepics-query into master

What does this MR do?

Related to #210553 (closed)

Fixes N+1 queries in group epics by using the Lookahead feature.

For example, when querying a group with a sample of 10 epics, we can see the number of queries against the namespaces table reduced by preloading the field :group unconditionally:

Query 
{
  group(fullPath: "gitlab-org") {
    epics {
      edges {
        node {
          title
        }
      }
    }
  }
}
Result 
{
  "data": {
    "group": {
      "epics": {
        "edges": [
          {
            "node": {
              "title": "Test Epic 10"
            }
          },
          {
            "node": {
              "title": "Test Epic 9"
            }
          },
          {
            "node": {
              "title": "Test Epic 8"
            }
          },
          {
            "node": {
              "title": "Test Epic 7"
            }
          },
          {
            "node": {
              "title": "Test Epic 6"
            }
          },
          {
            "node": {
              "title": "Test Epic 5"
            }
          },
          {
            "node": {
              "title": "Test Epic 4"
            }
          },
          {
            "node": {
              "title": "Test Epic 3"
            }
          },
          {
            "node": {
              "title": "Test Epic 2"
            }
          },
          {
            "node": {
              "title": "Test Epic 1"
            }
          }
        ]
      }
    }
  }
}
Log before preloading field 
Started POST "/api/graphql" for 127.0.0.1 at 2020-09-23 14:14:11 +0100
Processing by GraphqlController#execute as */*
  Parameters: {"query"=>"query {\n  group(fullPath: \"test-group\") {\n    epics {\n      edges {\n        node {\n          title\n        }\n      }\n    }\n  }\n}", "variables"=>"[FILTERED]", "graphql"=>{"query"=>"query {\n  group(fullPath: \"test-group\") {\n    epics {\n      edges {\n        node {\n          title\n        }\n      }\n    }\n  }\n}", "variables"=>"[FILTERED]"}}
  [1m[36mUser Load (0.5ms)[0m  [1m[34mSELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2[0m  [["id", 1], ["LIMIT", 1]]
  ↳ app/controllers/concerns/impersonation.rb:7:in `current_user'
Can't verify CSRF token authenticity.
  [1m[35m (0.1ms)[0m  [1m[35mBEGIN[0m
  ↳ app/models/user.rb:1315:in `ensure_namespace_correct'
  [1m[36mNamespace Load (0.4ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."owner_id" = $1 AND "namespaces"."type" IS NULL LIMIT $2[0m  [["owner_id", 1], ["LIMIT", 1]]
  ↳ app/models/user.rb:1315:in `ensure_namespace_correct'
  [1m[35m (0.2ms)[0m  [1m[35mCOMMIT[0m
  ↳ app/models/user.rb:753:in `forget_me!'
  [1m[35mSQL (0.7ms)[0m  [1m[34mSELECT "namespaces"."id" AS t0_r0, "namespaces"."name" AS t0_r1, "namespaces"."path" AS t0_r2, "namespaces"."owner_id" AS t0_r3, "namespaces"."created_at" AS t0_r4, "namespaces"."updated_at" AS t0_r5, "namespaces"."type" AS t0_r6, "namespaces"."description" AS t0_r7, "namespaces"."avatar" AS t0_r8, "namespaces"."membership_lock" AS t0_r9, "namespaces"."share_with_group_lock" AS t0_r10, "namespaces"."visibility_level" AS t0_r11, "namespaces"."request_access_enabled" AS t0_r12, "namespaces"."ldap_sync_status" AS t0_r13, "namespaces"."ldap_sync_error" AS t0_r14, "namespaces"."ldap_sync_last_update_at" AS t0_r15, "namespaces"."ldap_sync_last_successful_update_at" AS t0_r16, "namespaces"."ldap_sync_last_sync_at" AS t0_r17, "namespaces"."description_html" AS t0_r18, "namespaces"."lfs_enabled" AS t0_r19, "namespaces"."parent_id" AS t0_r20, "namespaces"."shared_runners_minutes_limit" AS t0_r21, "namespaces"."repository_size_limit" AS t0_r22, "namespaces"."require_two_factor_authentication" AS t0_r23, "namespaces"."two_factor_grace_period" AS t0_r24, "namespaces"."cached_markdown_version" AS t0_r25, "namespaces"."project_creation_level" AS t0_r26, "namespaces"."runners_token" AS t0_r27, "namespaces"."file_template_project_id" AS t0_r28, "namespaces"."saml_discovery_token" AS t0_r29, "namespaces"."runners_token_encrypted" AS t0_r30, "namespaces"."custom_project_templates_group_id" AS t0_r31, "namespaces"."auto_devops_enabled" AS t0_r32, "namespaces"."extra_shared_runners_minutes_limit" AS t0_r33, "namespaces"."last_ci_minutes_notification_at" AS t0_r34, "namespaces"."last_ci_minutes_usage_notification_level" AS t0_r35, "namespaces"."subgroup_creation_level" AS t0_r36, "namespaces"."emails_disabled" AS t0_r37, "namespaces"."max_pages_size" AS t0_r38, "namespaces"."max_artifacts_size" AS t0_r39, "namespaces"."mentions_disabled" AS t0_r40, "namespaces"."default_branch_protection" AS t0_r41, "namespaces"."unlock_membership_to_ldap" AS t0_r42, "namespaces"."max_personal_access_token_lifetime" AS t0_r43, "namespaces"."push_rule_id" AS t0_r44, "namespaces"."shared_runners_enabled" AS t0_r45, "namespaces"."allow_descendants_override_disabled_shared_runners" AS t0_r46, "namespaces"."traversal_ids" AS t0_r47, "namespaces"."delayed_project_removal" AS t0_r48, "routes"."id" AS t1_r0, "routes"."source_id" AS t1_r1, "routes"."source_type" AS t1_r2, "routes"."path" AS t1_r3, "routes"."created_at" AS t1_r4, "routes"."updated_at" AS t1_r5, "routes"."name" AS t1_r6 FROM "namespaces" LEFT OUTER JOIN "routes" ON "routes"."source_type" = $1 AND "routes"."source_id" = "namespaces"."id" WHERE "namespaces"."type" = $2 AND ((LOWER(routes.path) = LOWER('test-group')))[0m  [["source_type", "Namespace"], ["type", "Group"]]
  ↳ lib/gitlab/graphql/loaders/full_path_model_loader.rb:18:in `block in find'
  [1m[36mSamlProvider Load (0.2ms)[0m  [1m[34mSELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" = $1 LIMIT $2[0m  [["group_id", 100], ["LIMIT", 1]]
  ↳ ee/lib/gitlab/auth/group_saml/sso_enforcer.rb:35:in `group_access_restricted?'
  [1m[36mLicense Load (0.2ms)[0m  [1m[34mSELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT $1[0m  [["LIMIT", 100]]
  ↳ ee/app/models/license.rb:258:in `load_license'
  [1m[36mIpRestriction Load (0.2ms)[0m  [1m[34mSELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = $1[0m  [["group_id", 100]]
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  [1m[36mGroup Load (1.4ms)[0m  [1m[35mWITH RECURSIVE "base_and_ancestors" AS ((WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 100)
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces".* FROM "base_and_descendants" AS "namespaces")
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT namespaces.id, namespaces.parent_id, gitlab_subscriptions.hosted_plan_id FROM "base_and_ancestors" AS "namespaces" LEFT OUTER JOIN gitlab_subscriptions ON gitlab_subscriptions.namespace_id=namespaces.id[0m
  ↳ ee/lib/gitlab/group_plans_preloader.rb:20:in `each_with_object'
  [1m[36mPlan Load (0.3ms)[0m  [1m[34mSELECT "plans".* FROM "plans" WHERE 1=0[0m
  ↳ ee/lib/gitlab/group_plans_preloader.rb:46:in `each_with_object'
  [1m[36mGroup Load (0.8ms)[0m  [1m[35mWITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 100)
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces".* FROM "base_and_descendants" AS "namespaces"[0m
  ↳ ee/lib/gitlab/group_plans_preloader.rb:51:in `preload'
  [1m[36mEpic Load (0.6ms)[0m  [1m[34mSELECT "epics".* FROM "epics" WHERE "epics"."group_id" = $1 AND ("epics"."confidential" = $2 OR "epics"."confidential" = $3 AND "epics"."group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $4 AND (1=0))) ORDER BY "epics"."id" DESC LIMIT $5[0m  [["group_id", 100], ["confidential", false], ["confidential", true], ["type", "Group"], ["LIMIT", 2000]]
  ↳ lib/gitlab/graphql/pagination/keyset/connection.rb:100:in `nodes'
  [1m[36mGroup Load (0.3ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 LIMIT $3[0m  [["type", "Group"], ["id", 100], ["LIMIT", 1]]
  ↳ ee/app/policies/epic_policy.rb:6:in `block in <class:EpicPolicy>'
  [1m[36mCACHE Group Load (0.1ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 LIMIT $3[0m  [["type", "Group"], ["id", 100], ["LIMIT", 1]]
  ↳ ee/app/policies/epic_policy.rb:6:in `block in <class:EpicPolicy>'
  [1m[36mCACHE Group Load (0.0ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 LIMIT $3[0m  [["type", "Group"], ["id", 100], ["LIMIT", 1]]
  ↳ ee/app/policies/epic_policy.rb:6:in `block in <class:EpicPolicy>'
  [1m[36mCACHE Group Load (0.1ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 LIMIT $3[0m  [["type", "Group"], ["id", 100], ["LIMIT", 1]]
  ↳ ee/app/policies/epic_policy.rb:6:in `block in <class:EpicPolicy>'
  [1m[36mCACHE Group Load (0.0ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 LIMIT $3[0m  [["type", "Group"], ["id", 100], ["LIMIT", 1]]
  ↳ ee/app/policies/epic_policy.rb:6:in `block in <class:EpicPolicy>'
  [1m[36mCACHE Group Load (0.0ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 LIMIT $3[0m  [["type", "Group"], ["id", 100], ["LIMIT", 1]]
  ↳ ee/app/policies/epic_policy.rb:6:in `block in <class:EpicPolicy>'
  [1m[36mCACHE Group Load (0.1ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 LIMIT $3[0m  [["type", "Group"], ["id", 100], ["LIMIT", 1]]
  ↳ ee/app/policies/epic_policy.rb:6:in `block in <class:EpicPolicy>'
  [1m[36mCACHE Group Load (0.0ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 LIMIT $3[0m  [["type", "Group"], ["id", 100], ["LIMIT", 1]]
  ↳ ee/app/policies/epic_policy.rb:6:in `block in <class:EpicPolicy>'
  [1m[36mCACHE Group Load (0.0ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 LIMIT $3[0m  [["type", "Group"], ["id", 100], ["LIMIT", 1]]
  ↳ ee/app/policies/epic_policy.rb:6:in `block in <class:EpicPolicy>'
  [1m[36mCACHE Group Load (0.1ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 LIMIT $3[0m  [["type", "Group"], ["id", 100], ["LIMIT", 1]]
  ↳ ee/app/policies/epic_policy.rb:6:in `block in <class:EpicPolicy>'
Completed 200 OK in 90ms (Views: 0.2ms | ActiveRecord: 6.5ms | Elasticsearch: 0.0ms | Allocations: 92671)
Log after preloading field 
Started POST "/api/graphql" for 127.0.0.1 at 2020-09-23 14:15:37 +0100
Processing by GraphqlController#execute as */*
  Parameters: {"query"=>"query {\n  group(fullPath: \"test-group\") {\n    epics {\n      edges {\n        node {\n          title\n        }\n      }\n    }\n  }\n}", "variables"=>"[FILTERED]", "graphql"=>{"query"=>"query {\n  group(fullPath: \"test-group\") {\n    epics {\n      edges {\n        node {\n          title\n        }\n      }\n    }\n  }\n}", "variables"=>"[FILTERED]"}}
  [1m[36mUser Load (0.4ms)[0m  [1m[34mSELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2[0m  [["id", 1], ["LIMIT", 1]]
  ↳ app/controllers/concerns/impersonation.rb:7:in `current_user'
Can't verify CSRF token authenticity.
  [1m[35m (0.1ms)[0m  [1m[35mBEGIN[0m
  ↳ app/models/user.rb:1315:in `ensure_namespace_correct'
  [1m[36mNamespace Load (0.3ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."owner_id" = $1 AND "namespaces"."type" IS NULL LIMIT $2[0m  [["owner_id", 1], ["LIMIT", 1]]
  ↳ app/models/user.rb:1315:in `ensure_namespace_correct'
  [1m[35m (0.1ms)[0m  [1m[35mCOMMIT[0m
  ↳ app/models/user.rb:753:in `forget_me!'
  [1m[35mSQL (0.7ms)[0m  [1m[34mSELECT "namespaces"."id" AS t0_r0, "namespaces"."name" AS t0_r1, "namespaces"."path" AS t0_r2, "namespaces"."owner_id" AS t0_r3, "namespaces"."created_at" AS t0_r4, "namespaces"."updated_at" AS t0_r5, "namespaces"."type" AS t0_r6, "namespaces"."description" AS t0_r7, "namespaces"."avatar" AS t0_r8, "namespaces"."membership_lock" AS t0_r9, "namespaces"."share_with_group_lock" AS t0_r10, "namespaces"."visibility_level" AS t0_r11, "namespaces"."request_access_enabled" AS t0_r12, "namespaces"."ldap_sync_status" AS t0_r13, "namespaces"."ldap_sync_error" AS t0_r14, "namespaces"."ldap_sync_last_update_at" AS t0_r15, "namespaces"."ldap_sync_last_successful_update_at" AS t0_r16, "namespaces"."ldap_sync_last_sync_at" AS t0_r17, "namespaces"."description_html" AS t0_r18, "namespaces"."lfs_enabled" AS t0_r19, "namespaces"."parent_id" AS t0_r20, "namespaces"."shared_runners_minutes_limit" AS t0_r21, "namespaces"."repository_size_limit" AS t0_r22, "namespaces"."require_two_factor_authentication" AS t0_r23, "namespaces"."two_factor_grace_period" AS t0_r24, "namespaces"."cached_markdown_version" AS t0_r25, "namespaces"."project_creation_level" AS t0_r26, "namespaces"."runners_token" AS t0_r27, "namespaces"."file_template_project_id" AS t0_r28, "namespaces"."saml_discovery_token" AS t0_r29, "namespaces"."runners_token_encrypted" AS t0_r30, "namespaces"."custom_project_templates_group_id" AS t0_r31, "namespaces"."auto_devops_enabled" AS t0_r32, "namespaces"."extra_shared_runners_minutes_limit" AS t0_r33, "namespaces"."last_ci_minutes_notification_at" AS t0_r34, "namespaces"."last_ci_minutes_usage_notification_level" AS t0_r35, "namespaces"."subgroup_creation_level" AS t0_r36, "namespaces"."emails_disabled" AS t0_r37, "namespaces"."max_pages_size" AS t0_r38, "namespaces"."max_artifacts_size" AS t0_r39, "namespaces"."mentions_disabled" AS t0_r40, "namespaces"."default_branch_protection" AS t0_r41, "namespaces"."unlock_membership_to_ldap" AS t0_r42, "namespaces"."max_personal_access_token_lifetime" AS t0_r43, "namespaces"."push_rule_id" AS t0_r44, "namespaces"."shared_runners_enabled" AS t0_r45, "namespaces"."allow_descendants_override_disabled_shared_runners" AS t0_r46, "namespaces"."traversal_ids" AS t0_r47, "namespaces"."delayed_project_removal" AS t0_r48, "routes"."id" AS t1_r0, "routes"."source_id" AS t1_r1, "routes"."source_type" AS t1_r2, "routes"."path" AS t1_r3, "routes"."created_at" AS t1_r4, "routes"."updated_at" AS t1_r5, "routes"."name" AS t1_r6 FROM "namespaces" LEFT OUTER JOIN "routes" ON "routes"."source_type" = $1 AND "routes"."source_id" = "namespaces"."id" WHERE "namespaces"."type" = $2 AND ((LOWER(routes.path) = LOWER('test-group')))[0m  [["source_type", "Namespace"], ["type", "Group"]]
  ↳ lib/gitlab/graphql/loaders/full_path_model_loader.rb:18:in `block in find'
  [1m[36mSamlProvider Load (0.2ms)[0m  [1m[34mSELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" = $1 LIMIT $2[0m  [["group_id", 100], ["LIMIT", 1]]
  ↳ ee/lib/gitlab/auth/group_saml/sso_enforcer.rb:35:in `group_access_restricted?'
  [1m[36mLicense Load (0.2ms)[0m  [1m[34mSELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT $1[0m  [["LIMIT", 100]]
  ↳ ee/app/models/license.rb:258:in `load_license'
  [1m[36mIpRestriction Load (0.2ms)[0m  [1m[34mSELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = $1[0m  [["group_id", 100]]
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  [1m[36mGroup Load (1.3ms)[0m  [1m[35mWITH RECURSIVE "base_and_ancestors" AS ((WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 100)
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces".* FROM "base_and_descendants" AS "namespaces")
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT namespaces.id, namespaces.parent_id, gitlab_subscriptions.hosted_plan_id FROM "base_and_ancestors" AS "namespaces" LEFT OUTER JOIN gitlab_subscriptions ON gitlab_subscriptions.namespace_id=namespaces.id[0m
  ↳ ee/lib/gitlab/group_plans_preloader.rb:20:in `each_with_object'
  [1m[36mPlan Load (0.2ms)[0m  [1m[34mSELECT "plans".* FROM "plans" WHERE 1=0[0m
  ↳ ee/lib/gitlab/group_plans_preloader.rb:46:in `each_with_object'
  [1m[36mGroup Load (0.8ms)[0m  [1m[35mWITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 100)
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces".* FROM "base_and_descendants" AS "namespaces"[0m
  ↳ ee/lib/gitlab/group_plans_preloader.rb:51:in `preload'
  [1m[36mEpic Load (0.5ms)[0m  [1m[34mSELECT "epics".* FROM "epics" WHERE "epics"."group_id" = $1 AND ("epics"."confidential" = $2 OR "epics"."confidential" = $3 AND "epics"."group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $4 AND (1=0))) ORDER BY "epics"."id" DESC LIMIT $5[0m  [["group_id", 100], ["confidential", false], ["confidential", true], ["type", "Group"], ["LIMIT", 2000]]
  ↳ lib/gitlab/graphql/pagination/keyset/connection.rb:100:in `nodes'
  [1m[36mGroup Load (0.3ms)[0m  [1m[34mSELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2[0m  [["type", "Group"], ["id", 100]]
  ↳ lib/gitlab/graphql/pagination/keyset/connection.rb:100:in `nodes'
Completed 200 OK in 64ms (Views: 0.2ms | ActiveRecord: 5.5ms | Elasticsearch: 0.0ms | Allocations: 60235)

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by Mark Chao

Merge request reports