Skip to content
Snippets Groups Projects

Speed up Open list when there are assignee lists

Merged Sean McGivern requested to merge speed-up-open-list-for-boards-with-assignee-lists into master
2 unresolved threads

What does this MR do?

When there is an assignee list and the board can show a lot of issues, the Open list can be very slow. This was because the query was essentially repeated inside a NOT IN to exclude issues that are assigned to someone from one of the assignee lists.

Instead, we can simplify the inner query to be a more direct one:

NOT EXISTS (
  SELECT 1
  FROM issue_assignees
  WHERE user_id IN ($assignee_list_users)
    AND issue_id = issues.id
)

Which also performs much better, as well as being simpler to understand.

What are the relevant issue numbers?

Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/51184.

Does this MR meet the acceptance criteria?

Merge request reports

Loading
Loading

Activity

Filter activity
  • Approvals
  • Assignees & reviewers
  • Comments (from bots)
  • Comments (from users)
  • Commits & branches
  • Edits
  • Labels
  • Lock status
  • Mentions
  • Merge request status
  • Tracking
60 60 def without_assignees_from_lists(issues)
61 61 return issues if all_assignee_lists.empty?
62 62
63 issues
64 .where.not(id: issues.joins(:assignees).where(users: { id: all_assignee_lists.select(:user_id) }))
63 matching_assignee = ::IssueAssignee
64 .where(user_id: all_assignee_lists.reorder(nil).select(:user_id))
65 .where("issue_id = issues.id")
66 .select(1)
67
68 issues.where('NOT EXISTS (?)', matching_assignee)
  • Author Contributor
    Old query
    SELECT issues.*,
      (
        SELECT MIN("label_priorities"."priority")
        FROM "labels"
        INNER JOIN "label_links" ON "label_links"."label_id" = "labels"."id"
        LEFT JOIN "label_priorities" ON "labels"."id" = "label_priorities"."label_id"
        WHERE (label_priorities.project_id = issues.project_id)
          AND (label_links.target_id = issues.id)
          AND "label_links"."target_type" = 'Issue'
        ) AS highest_priority
    FROM "issues"
    INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
    INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id"
      AND "label_links"."target_type" = 'Issue'
    INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
    LEFT JOIN project_features ON projects.id = project_features.project_id
    WHERE "projects"."namespace_id" IN (
        WITH RECURSIVE "base_and_descendants" AS (
            SELECT "namespaces".*
            FROM "namespaces"
            WHERE "namespaces"."type" IN ('Group')
              AND "namespaces"."id" = 9970
            
            UNION
            
            SELECT "namespaces".*
            FROM "namespaces",
              "base_and_descendants"
            WHERE "namespaces"."type" IN ('Group')
              AND "namespaces"."parent_id" = "base_and_descendants"."id"
            )
        SELECT "id"
        FROM "base_and_descendants" AS "namespaces"
        )
      AND (
        EXISTS (
          SELECT 1
          FROM "project_authorizations"
          WHERE "project_authorizations"."user_id" = 443319
            AND (project_authorizations.project_id = projects.id)
          )
        OR projects.visibility_level IN (
          10,
          20
          )
        )
      AND (
        "project_features"."issues_access_level" IN (
          NULL,
          20,
          30
          )
        OR (
          "project_features"."issues_access_level" = 10
          AND EXISTS (
            SELECT 1
            FROM "project_authorizations"
            WHERE "project_authorizations"."user_id" = 443319
              AND (project_authorizations.project_id = projects.id)
            )
          )
        )
      AND ("issues"."state" IN ('opened'))
      AND "projects"."archived" = 'f'
      AND "labels"."title" IN (
        'Plan',
        'frontend'
        )
      AND (
        "issues"."id" NOT IN (
          SELECT "issues"."id"
          FROM "issues"
          INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
          INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id"
            AND "label_links"."target_type" = 'Issue'
          INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
          INNER JOIN "issue_assignees" ON "issue_assignees"."issue_id" = "issues"."id"
          INNER JOIN "users" ON "users"."id" = "issue_assignees"."user_id"
          LEFT JOIN project_features ON projects.id = project_features.project_id
          WHERE "projects"."namespace_id" IN (
              WITH RECURSIVE "base_and_descendants" AS (
                  SELECT "namespaces".*
                  FROM "namespaces"
                  WHERE "namespaces"."type" IN ('Group')
                    AND "namespaces"."id" = 9970
                  
                  UNION
                  
                  SELECT "namespaces".*
                  FROM "namespaces",
                    "base_and_descendants"
                  WHERE "namespaces"."type" IN ('Group')
                    AND "namespaces"."parent_id" = "base_and_descendants"."id"
                  )
              SELECT "id"
              FROM "base_and_descendants" AS "namespaces"
              )
            AND (
              EXISTS (
                SELECT 1
                FROM "project_authorizations"
                WHERE "project_authorizations"."user_id" = 443319
                  AND (project_authorizations.project_id = projects.id)
                )
              OR projects.visibility_level IN (
                10,
                20
                )
              )
            AND (
              "project_features"."issues_access_level" IN (
                NULL,
                20,
                30
                )
              OR (
                "project_features"."issues_access_level" = 10
                AND EXISTS (
                  SELECT 1
                  FROM "project_authorizations"
                  WHERE "project_authorizations"."user_id" = 443319
                    AND (project_authorizations.project_id = projects.id)
                  )
                )
              )
            AND ("issues"."state" IN ('opened'))
            AND "projects"."archived" = 'f'
            AND "labels"."title" IN (
              'Plan',
              'frontend'
              )
            AND "users"."id" IN (
              SELECT "lists"."user_id"
              FROM "lists"
              WHERE "lists"."board_id" = 654688
                AND "lists"."list_type" = 3
                AND ("lists"."user_id" IS NOT NULL)
              ORDER BY "lists"."list_type" ASC,
                "lists"."position" ASC
              )
          GROUP BY "issues"."id"
          HAVING (COUNT(DISTINCT labels.title) = 2)
          ORDER BY "issues"."id" DESC
          )
        )
    GROUP BY "issues"."id"
    HAVING (COUNT(DISTINCT labels.title) = 2)
    ORDER BY relative_position ASC NULLS LAST,
      highest_priority ASC NULLS LAST,
      id DESC LIMIT 21 OFFSET 0
    New query
    SELECT issues.*,
      (
        SELECT MIN("label_priorities"."priority")
        FROM "labels"
        INNER JOIN "label_links" ON "label_links"."label_id" = "labels"."id"
        LEFT JOIN "label_priorities" ON "labels"."id" = "label_priorities"."label_id"
        WHERE (label_priorities.project_id = issues.project_id)
          AND (label_links.target_id = issues.id)
          AND "label_links"."target_type" = 'Issue'
        ) AS highest_priority
    FROM "issues"
    INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
    INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id"
      AND "label_links"."target_type" = 'Issue'
    INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
    LEFT JOIN project_features ON projects.id = project_features.project_id
    WHERE "projects"."namespace_id" IN (
        WITH RECURSIVE "base_and_descendants" AS (
            SELECT "namespaces".*
            FROM "namespaces"
            WHERE "namespaces"."type" IN ('Group')
              AND "namespaces"."id" = 9970
            
            UNION
            
            SELECT "namespaces".*
            FROM "namespaces",
              "base_and_descendants"
            WHERE "namespaces"."type" IN ('Group')
              AND "namespaces"."parent_id" = "base_and_descendants"."id"
            )
        SELECT "id"
        FROM "base_and_descendants" AS "namespaces"
        )
      AND (
        EXISTS (
          SELECT 1
          FROM "project_authorizations"
          WHERE "project_authorizations"."user_id" = 443319
            AND (project_authorizations.project_id = projects.id)
          )
        OR projects.visibility_level IN (
          10,
          20
          )
        )
      AND (
        "project_features"."issues_access_level" IN (
          NULL,
          20,
          30
          )
        OR (
          "project_features"."issues_access_level" = 10
          AND EXISTS (
            SELECT 1
            FROM "project_authorizations"
            WHERE "project_authorizations"."user_id" = 443319
              AND (project_authorizations.project_id = projects.id)
            )
          )
        )
      AND ("issues"."state" IN ('opened'))
      AND "projects"."archived" = 'f'
      AND "labels"."title" IN (
        'Plan',
        'frontend'
        )
      AND (
        NOT (
          EXISTS (
            SELECT 1
            FROM "issue_assignees"
            WHERE "issue_assignees"."user_id" IN (
                SELECT "lists"."user_id"
                FROM "lists"
                WHERE "lists"."board_id" = 654688
                  AND "lists"."list_type" = 3
                  AND ("lists"."user_id" IS NOT NULL)
                )
              AND (issue_id = issues.id)
            )
          )
        )
    GROUP BY "issues"."id"
    HAVING (COUNT(DISTINCT labels.title) = 2)
    ORDER BY relative_position ASC NULLS LAST,
      highest_priority ASC NULLS LAST,
      id DESC LIMIT 21 OFFSET 0

    Neither is great! But the first takes over 15s (it times out), and the second takes around 700ms :slight_smile:

  • Author Contributor

    The existing specs in ee/spec/services/ee/boards/issues/list_service_spec.rb already exercise this pretty well; I can break them by removing any condition from this query.

    I also tested manually in a production console to make sure the first page matches the expected first page.

  • Please register or sign in to reply
  • 1 Warning
    :warning: You’ve made some app changes, but didn’t add any tests.
    That’s OK as long as you’re refactoring existing code,
    but please consider adding any of the ~backstage, ~Documentation, QA labels.
    1 Message
    :book: Consider setting merge_request to 9090 in ee/changelogs/unreleased/speed-up-open-list-for-boards-with-assignee-lists.yml. See the documentation.

    Generated by :no_entry_sign: Danger

    Edited by 🤖 GitLab Bot 🤖
  • Thanks @smcgivern, I think the ruby for building the query became more readable as well :smile:

    Could you rebase since https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/24266 is fixed? Then we can get a green pipeline here :grin:.

    @rspeicher WDYT?

  • Bob Van Landuyt approved this merge request

    approved this merge request

  • Sean McGivern added 14 commits

    added 14 commits

    Compare with previous version

  • Robert Speicher resolved all discussions

    resolved all discussions

  • Robert Speicher mentioned in commit 53d1cae3

    mentioned in commit 53d1cae3

  • Robert Speicher resolved all discussions

    resolved all discussions

  • Author Contributor

    Thanks! I will create an exception request tomorrow.

  • Yorick Peterse added 1 deleted label

    added 1 deleted label

  • Yorick Peterse changed milestone to %11.7

    changed milestone to %11.7

  • Automatically picked into https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/9154, will merge into 11-7-stable-ee ready for 11.7.0-rc6-ee.

  • GitLab Release Tools Bot removed 1 deleted label

    removed 1 deleted label

  • Robert Speicher mentioned in commit f1f0893a

    mentioned in commit f1f0893a

  • mentioned in merge request !9154 (merged)

  • Please register or sign in to reply
    Loading