Remove n+1 queries from GraphQL EpicType (!27551) · Merge requests · GitLab.org / GitLab

charlie ablett requested to merge cablett-graphql-epic-n+1 into master Mar 19, 2020

What does this MR do?

Removes N+1 queries from two fields on EpicType (has_issues and has_children).

Also expands the LazyEpicAggregate to receive a block for greater extensibility.

Screenshots

No screenshots, but here is the relevant GraphQL query on my local:

query {
  group(fullPath: "group") {
    epics(iids: [1,2,3]) {
      nodes {
        id
        iid
        hasIssues
        hasChildren
      }
    }
  }
}

With feature flag on

  Group Load (0.8ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 52 AND "namespaces"."parent_id" IS NULL LIMIT 1
  ↳ app/models/namespace.rb:282:in `block in root_ancestor'
  License Load (0.7ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 1
  ↳ ee/app/models/license.rb:269:in `load_license'
  IpRestriction Load (0.5ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 52
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 52 LIMIT 1
  ↳ ee/app/finders/epics_finder.rb:74:in `group'
  Epic Load (1.6ms)  SELECT "epics".* FROM "epics" WHERE "epics"."group_id" = 52 AND "epics"."iid" IN (1, 2, 3) ORDER BY "epics"."id" DESC LIMIT 2000
  ↳ lib/gitlab/graphql/connections/keyset/connection.rb:57:in `paged_nodes'
  Group Load (0.6ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 52 LIMIT 1
  ↳ ee/app/policies/epic_policy.rb:4:in `block in <class:EpicPolicy>'
  Group Load (0.7ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 52 LIMIT 1
  ↳ ee/app/policies/epic_policy.rb:4:in `block in <class:EpicPolicy>'
  Group Load (0.6ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 52 LIMIT 1
  ↳ ee/app/policies/epic_policy.rb:4:in `block in <class:EpicPolicy>'
  Epic Load (7.0ms)  WITH RECURSIVE "base_and_descendants" AS ((SELECT "epics".* FROM "epics" WHERE "epics"."id" IN (38, 37, 36))
UNION
(SELECT "epics".* FROM "epics", "base_and_descendants" WHERE "epics"."parent_id" = "base_and_descendants"."id")) SELECT epics.id, epics.iid, epics.parent_id, epics.state_id AS epic_state_id, issues.state_id AS issues_state_id, COUNT(issues) AS issues_count, SUM(COALESCE(issues.weight, 0)) AS issues_weight_sum FROM "base_and_descendants" AS "epics" LEFT OUTER JOIN "epic_issues" ON "epic_issues"."epic_id" = "epics"."id" LEFT OUTER JOIN "issues" ON "issues"."id" = "epic_issues"."issue_id" GROUP BY issues.state_id, epics.id, epics.iid, epics.parent_id, epics.state_id LIMIT 100001
  ↳ ee/lib/gitlab/graphql/loaders/bulk_epic_aggregate_loader.rb:32:in `map'

With feature flag off

  Group Load (0.6ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 52 AND "namespaces"."parent_id" IS NULL LIMIT 1
  ↳ app/models/namespace.rb:282:in `block in root_ancestor'
  License Load (0.6ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 1
  ↳ ee/app/models/license.rb:269:in `load_license'
  IpRestriction Load (0.5ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 52
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Group Load (0.6ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 52 LIMIT 1
  ↳ ee/app/finders/epics_finder.rb:74:in `group'
  Epic Load (0.9ms)  SELECT "epics".* FROM "epics" WHERE "epics"."group_id" = 52 AND "epics"."iid" IN (1, 2, 3) ORDER BY "epics"."id" DESC LIMIT 2000
  ↳ lib/gitlab/graphql/connections/keyset/connection.rb:57:in `paged_nodes'
  Group Load (0.9ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 52 LIMIT 1
  ↳ ee/app/policies/epic_policy.rb:4:in `block in <class:EpicPolicy>'
  Group Load (1.0ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 52 LIMIT 1
  ↳ ee/app/policies/epic_policy.rb:4:in `block in <class:EpicPolicy>'
  Group Load (1.7ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 52 LIMIT 1
  ↳ ee/app/policies/epic_policy.rb:4:in `block in <class:EpicPolicy>'
  Feature::FlipperGate Load (1.7ms)  SELECT "feature_gates".* FROM "feature_gates" WHERE "feature_gates"."feature_key" = 'unfiltered_epic_aggregates'
  ↳ lib/feature.rb:67:in `enabled?'
  Issue Exists? (2.7ms)  SELECT 1 AS one FROM "issues" INNER JOIN "epic_issues" ON "issues"."id" = "epic_issues"."issue_id" WHERE "epic_issues"."epic_id" = 38 LIMIT 1
  ↳ ee/app/models/ee/epic.rb:319:in `has_issues?'
  Epic Exists? (1.7ms)  SELECT 1 AS one FROM "epics" WHERE "epics"."parent_id" = 38 LIMIT 1
  ↳ ee/app/models/ee/epic.rb:315:in `has_children?'
  Issue Exists? (1.7ms)  SELECT 1 AS one FROM "issues" INNER JOIN "epic_issues" ON "issues"."id" = "epic_issues"."issue_id" WHERE "epic_issues"."epic_id" = 37 LIMIT 1
  ↳ ee/app/models/ee/epic.rb:319:in `has_issues?'
  Epic Exists? (1.5ms)  SELECT 1 AS one FROM "epics" WHERE "epics"."parent_id" = 37 LIMIT 1
  ↳ ee/app/models/ee/epic.rb:315:in `has_children?'
  Issue Exists? (0.5ms)  SELECT 1 AS one FROM "issues" INNER JOIN "epic_issues" ON "issues"."id" = "epic_issues"."issue_id" WHERE "epic_issues"."epic_id" = 36 LIMIT 1
  ↳ ee/app/models/ee/epic.rb:319:in `has_issues?'
  Epic Exists? (0.3ms)  SELECT 1 AS one FROM "epics" WHERE "epics"."parent_id" = 36 LIMIT 1
  ↳ ee/app/models/ee/epic.rb:315:in `has_children?'

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

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 Nov 08, 2021 by 🤖 GitLab Bot 🤖

Admin message

Remove n+1 queries from GraphQL EpicType