Skip to content

Use lazy aggregation for epics healthStatus

Stanislav Dobrovolschii requested to merge 372118-graphql-use-lazy-aggregation-for-epics-healthstatus into master

What does this MR do and why?

To decrease the amount of DB queries, MR changes the way health status counters are getting loaded for epics. Instead of getting loaded per epic it uses the LazyEpicAggregate and fetches health status together with other lazy loaded fields (has_children, has_issues, descendant_counts, descendant_weight_sum) all at once.

DB queries

Epic.issue_metadata_for_epics

Queries

Before

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
    "epics"
    LEFT OUTER JOIN "epic_issues" ON "epic_issues"."epic_id" = "epics"."id"
    LEFT OUTER JOIN "issues" ON "issues"."id" = "epic_issues"."issue_id"
WHERE
    "epics"."id" IN (427, 428)
GROUP BY
    "epics"."id",
    "epics"."iid",
    "epics"."parent_id",
    "epics"."state_id",
    "issues"."state_id"
LIMIT
    100;

After

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,
    COUNT(issues) filter (
        where
            issues.health_status = 1
    ) AS health_on_track,
    COUNT(issues) filter (
        where
            issues.health_status = 2
    ) AS health_needs_attention,
    COUNT(issues) filter (
        where
            issues.health_status = 3
    ) AS health_at_risk
FROM
    "epics"
    LEFT OUTER JOIN "epic_issues" ON "epic_issues"."epic_id" = "epics"."id"
    LEFT OUTER JOIN "issues" ON "issues"."id" = "epic_issues"."issue_id"
WHERE
    "epics"."id" IN (427, 428)
GROUP BY
    "epics"."id",
    "epics"."iid",
    "epics"."parent_id",
    "epics"."state_id",
    "issues"."state_id"
LIMIT
    100;

How to set up and validate locally

To reproduce DB queries you can use the following GraphQL request ⬇

Request

Query

query ($fullPath: ID!, $iids: [ID!], $pageSize: Int = 100, $epicEndCursor: String = "") {
  group(fullPath: $fullPath) {
    id
    path
    fullPath
    epics(iids: $iids) {
      nodes {
        children(first: $pageSize, after: $epicEndCursor) {
          edges {
            node {
              __typename
              healthStatus {
                issuesAtRisk
                issuesOnTrack
                issuesNeedingAttention
              }
            }
            __typename
          }
          __typename
        }
        __typename
      }
    }
    __typename
  }
}

Variables (please use the iids and fullPath of group/epics that have epics with child issues)

{
  "epicEndCursor": "",
  "iids": ["123"],
  "fullPath": "gitlab-org"
}

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Closes #372118 (closed)

Edited by Stanislav Dobrovolschii

Merge request reports