Speed up group milestone index by passing group_id to IssuesFinder (!8363) · Merge requests · GitLab.org / GitLab FOSS

Adam Niedzielski requested to merge speed-up-group-milestone-index into master Dec 29, 2016

What does this MR do?

It improves performance in group milestone index page by passing a hint to IssuesFinder. IssuesFinder generates a more performant query when it is scoped to a single group.

Versions affected

8.13, 8.14. 8.15

Why was this MR needed?

Loading group milestone index is not performant for a bigger dataset. See #25748 (closed)

Comparison of the generated query

I used the dataset proposed here $33946.

Before

   (1590.1ms)  SELECT COUNT(*) AS count_all, "issues"."state" AS issues_state FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
 AND "projects"."pending_delete" = $1 WHERE "issues"."deleted_at" IS NULL AND (
      issues.confidential IS NULL
      OR issues.confidential IS FALSE
      OR (issues.confidential = TRUE
        AND (issues.author_id = 10
          OR issues.assignee_id = 10
          OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."
project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 10 AND (project_authorizations.access_level >= 20)  ORDER BY "p
rojects"."id" DESC)))) AND "projects"."pending_delete" = $2 AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" 
ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 10
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "projects"."id" IN (SELECT "projects"."id" FROM "projects" LEFT
 JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM
 "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "proj
ect_authorizations"."user_id" = 10
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND ("project_features"."issues_access_level" = 20 OR "project_feat
ures"."issues_access_level" IS NULL)
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" LEFT JOIN project_feat
ures ON projects.id = project_features.project_id WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "proj
ects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 10
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "project_authorizations"."user_id" = 10 AND "projects"."pending
_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"
."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 10
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "project_features"."issues_access_level" = 10) AND "issues"."milestone_id" IN (SELECT "milestones"."id" FROM "milestones" WHERE "milestones"."id" = 39  ORDER BY "milestones"."id" DESC) GROUP BY "issues"."state"  [["pending_delete", "f"], ["pending_delete", "f"]]

After

   (7.8ms)  SELECT COUNT(*) AS count_all, "issues"."state" AS issues_state FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" AN
D "projects"."pending_delete" = $1 WHERE "issues"."deleted_at" IS NULL AND (
      issues.confidential IS NULL
      OR issues.confidential IS FALSE
      OR (issues.confidential = TRUE
        AND (issues.author_id = 10
          OR issues.assignee_id = 10
          OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."
project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 10 AND (project_authorizations.access_level >= 20)  ORDER BY "p
rojects"."id" DESC)))) AND "projects"."pending_delete" = $2 AND (projects.id IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = 
'f' AND "projects"."namespace_id" = 2
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "project_group_links" ON "projects"."id" = "project_group_links"."project_id" WHERE "projects"."pending_de
lete" = 'f' AND "project_group_links"."group_id" = 2)) AND "projects"."id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects
.id = project_features.project_id WHERE "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."pend
ing_delete" = 'f' AND "projects"."namespace_id" = 2
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "project_group_links" ON "projects"."id" = "project_group_links"."project_id" WHERE "projects"."pending_de
lete" = 'f' AND "project_group_links"."group_id" = 2)) AND ("project_features"."issues_access_level" = 20 OR "project_features"."issues_access_level" IS NUL
L)
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" LEFT JOIN project_feat
ures ON projects.id = project_features.project_id WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = 'f' AND 
"projects"."namespace_id" = 2
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "project_group_links" ON "projects"."id" = "project_group_links"."project_id" WHERE "projects"."pending_de
lete" = 'f' AND "project_group_links"."group_id" = 2)) AND "project_authorizations"."user_id" = 10 AND "projects"."pending_delete" = 'f' AND (projects.id IN
 (SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = 'f' AND "projects"."namespace_id" = 2
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "project_group_links" ON "projects"."id" = "project_group_links"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_group_links"."group_id" = 2)) AND "project_features"."issues_access_level" = 10) AND "issues"."milestone_id" IN (SELECT "milestones"."id" FROM "milestones" WHERE "milestones"."id" = 39  ORDER BY "milestones"."id" DESC) GROUP BY "issues"."state"  [["pending_delete", "f"], ["pending_delete", "f"]]

Does this MR meet the acceptance criteria?

Changelog entry added
~~[ ] Documentation created/updated~~
~~[ ] API support added~~
Tests
- Added for this feature/bug
- All builds are passing
Conform by the merge request performance guides
Conform by the style guides
Branch has no merge conflicts with master (if it does - rebase it please)
Squashed related commits together

What are the relevant issue numbers?

Refers to #25748 (closed)

Speed up group milestone index by passing group_id to IssuesFinder