Skip to content

Improve epic swimlanes performance

Nicolas Dular requested to merge nd/board-epic-grouped-performance into master

What does this MR do and why?

Issue: Group by Epic on the Issue Board consistently f... (#367889 - closed)

This aims to improve the epic swimlanes performance on issue boards. It rewrites the query using a CTE for all the epics a user has access to and an EXISTS query where it JOINs the issues with the allowed epics.

We're also sorting the allowed epics as it's then in the order expected by the resolver.

Database queries

Infra Board

GitLab Org Board

However, we don't use the query in the case there is a filter applied...

With filters

Unfortunately we sometimes get a way less performant query when there are issue filters applied as the data is not in our favour (only a few issues matching with many epics): https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13242/commands/46454.

We therefore don't apply the optimization for now when filters are applied.

Resulting Query

Query 
WITH "sorted_epics" AS MATERIALIZED (
  SELECT
    "epics".*
  FROM
    "epics"
  WHERE
    "epics"."group_id" IN (
      SELECT
        "namespaces"."id"
      FROM
        "namespaces"
      WHERE
        "namespaces"."type" = 'Group'
        AND (
          traversal_ids @> ('{12125409}')
          OR "namespaces"."id" IN (6543, 1112072)
        )
    )
    AND "epics"."state_id" = 1
  ORDER BY
    "epics"."id" DESC
)
SELECT
  *
FROM
  sorted_epics
WHERE
  (
    EXISTS (
      SELECT
        "issues".*
      FROM
        "issues"
        INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
        INNER JOIN "epic_issues" ON "epic_issues"."issue_id" = "issues"."id"
        LEFT JOIN project_features ON projects.id = project_features.project_id
      WHERE
        "issues"."author_id" NOT IN (
          SELECT
            "banned_users"."user_id"
          FROM
            "banned_users"
        )
        AND (
          issues.confidential = FALSE
          OR (
            issues.confidential = TRUE
            AND (
              issues.author_id = 3509693
              OR EXISTS (
                SELECT
                  TRUE
                FROM
                  issue_assignees
                WHERE
                  user_id = 3509693
                  AND issue_id = issues.id
              )
              OR EXISTS (
                SELECT
                  1
                FROM
                  "project_authorizations"
                WHERE
                  "project_authorizations"."user_id" = 3509693
                  AND (
                    project_authorizations.project_id = issues.project_id
                  )
                  AND (project_authorizations.access_level >= 20)
              )
            )
          )
        )
        AND "projects"."namespace_id" IN (
          SELECT
            namespaces.traversal_ids [array_length(namespaces.traversal_ids, 1)] AS id
          FROM
            "namespaces"
          WHERE
            "namespaces"."type" = 'Group'
            AND (traversal_ids @> ('{12125409}'))
        )
        AND (
          EXISTS (
            SELECT
              1
            FROM
              "project_authorizations"
            WHERE
              "project_authorizations"."user_id" = 3509693
              AND (project_authorizations.project_id = projects.id)
              AND (project_authorizations.access_level >= 10)
          )
          OR projects.visibility_level IN (10, 20)
        )
        AND (
          "project_features"."issues_access_level" IS NULL
          OR "project_features"."issues_access_level" IN (20, 30)
          OR (
            "project_features"."issues_access_level" = 10
            AND EXISTS (
              SELECT
                1
              FROM
                "project_authorizations"
              WHERE
                "project_authorizations"."user_id" = 3509693
                AND (
                  project_authorizations.project_id = project_features.project_id
                )
                AND (project_authorizations.access_level >= 10)
            )
          )
        )
        AND "projects"."archived" = FALSE
        AND "issues"."issue_type" IN (0, 1)
        AND "epic_issues"."epic_id" = "sorted_epics"."id"
    )
  )
LIMIT
  10 OFFSET 0

How to set up and validate locally

  1. Make sure the traversal_ids feature flag is enabled (it's enabled for 100% of .com, but not by default): 
    Feature.enable(:use_traversal_ids_for_self_and_hierarchy)
Feature.enable(:use_traversal_ids_for_ancestors)
  2. Enable the feature flag for this MR 
    Feature.enable(:board_grouped_by_epic_performance)
  3. Go to Issues > Boards
  4. Enable the performance bar (keyboard shortcut p b)
  5. Select Group by: Epics on the top right
  6. Refresh the page and check the BoardEE queries in the performance bar that has the sorted_epics query in it
  7. Compare the query when board_grouped_by_epic_performance is disabled

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 Nicolas Dular

Merge request reports