Speed up Open list when there are assignee lists
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?
-
Changelog entry added, if necessary -
Conforms to the code review guidelines -
Conforms to the merge request performance guidelines -
Conforms to the style guides -
Conforms to the database guides -
EE specific content should be in the top level /ee
folder -
Security reports checked/validated by reviewer
Merge request reports
Activity
Note to self: create an exception request if the reviewers agree. cc @winh
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) 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
1 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 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
DangerEdited by 🤖 GitLab Bot 🤖- Resolved by Sean McGivern
https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/24266 fixes the failure.
@reprazent could you review this, please?
assigned to @reprazent
Thanks @smcgivern, I think the ruby for building the query became more readable as well
Could you rebase since https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/24266 is fixed? Then we can get a green pipeline here
.@rspeicher WDYT?
assigned to @rspeicher
added 14 commits
-
91f4481a...9eadb163 - 13 commits from branch
master
- 934e065d - Speed up Open list when there are assignee lists
-
91f4481a...9eadb163 - 13 commits from branch
@smcgivern @reprazent LGTM, thanks!
mentioned in commit 53d1cae3
mentioned in issue gitlab-org/release/tasks#634 (closed)
Exception request approved in gitlab-org/release/tasks#634 (closed).
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 for11.7.0-rc6-ee
.mentioned in commit f1f0893a
mentioned in merge request !9154 (merged)
mentioned in issue gitlab-org/release/tasks#669 (closed)
added devopsplan label
added Enterprise Edition label
added Category:Team Planning label
removed Plan [DEPRECATED] label