Skip to content

Draft: Improve performance of Epic swimlanes

Jarka Košanová requested to merge 367889-improve-epic-swimlanes into master

What does this MR do and why?

This MR optimises the sql query for retrieving epics of an issue board when grouping by epics.

In the original query we did the permissions check for all issues that are assigned to a board. But that is not needed as we need only epics, nothing more.

Worst case scenario is that we return an epic a user can access but it won't have any issues a user can access. I think there are two possibilities how this can happen:

  • a project is public/internal, user is not its member, but all issues in the epic are confidential
  • user is a guest of a project (even a private one), but all issues in the epic are confidential

These cases are rare and can be handled by frontend - follow-up issue:

Database queries

Problematic board

Query before the change

Query after the change

Query 
SELECT "epics".*
FROM "epics"
WHERE "epics"."id" IN
    (SELECT DISTINCT "epic_issues"."epic_id"
     FROM "issues"
     INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
     INNER JOIN "epic_issues" ON "epic_issues"."issue_id" = "issues"."id"
     WHERE "projects"."namespace_id" IN
         (SELECT "namespaces"."id"
          FROM "namespaces"
          WHERE "namespaces"."type" = 'Group'
            AND (traversal_ids @> ('{12125409}')))
       AND "projects"."archived" = FALSE
       AND "issues"."issue_type" IN (0,
                                     1)
       AND "epic_issues"."epic_id" IN
         (SELECT "epics"."id"
          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
LIMIT 10

Gitlab.org board example

Query before the change

Query after the change

Query 
SELECT "epics".*
FROM "epics"
WHERE "epics"."id" IN
    (SELECT DISTINCT "epic_issues"."epic_id"
     FROM "issues"
     INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
     INNER JOIN "epic_issues" ON "epic_issues"."issue_id" = "issues"."id"
     WHERE "projects"."namespace_id" IN
         (SELECT "namespaces"."id"
          FROM "namespaces"
          WHERE "namespaces"."type" = 'Group'
            AND (traversal_ids @> ('{9970}')))
       AND "projects"."archived" = FALSE
       AND EXISTS
         (SELECT "label_links".*
          FROM "label_links"
          WHERE "label_links"."target_type" = 'Issue'
            AND "label_links"."target_id" = "issues"."id"
            AND "label_links"."label_id" = 3103451)
       AND EXISTS
         (SELECT "label_links".*
          FROM "label_links"
          WHERE "label_links"."target_type" = 'Issue'
            AND "label_links"."target_id" = "issues"."id"
            AND "label_links"."label_id" = 10690700)
       AND "issues"."issue_type" IN (0,
                                     1)
       AND "epic_issues"."epic_id" IN
         (SELECT "epics"."id"
          FROM "epics"
          WHERE "epics"."group_id" IN
              (SELECT "namespaces"."id"
               FROM "namespaces"
               WHERE "namespaces"."type" = 'Group'
                 AND (traversal_ids @> ('{9970}')
                      OR 1=0))
            AND "epics"."state_id" = 1))
ORDER BY "epics"."id" DESC
LIMIT 10

MR acceptance checklist

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

Related to #367889 (closed)

Edited by Jarka Košanová

Merge request reports