Skip to content

Speed up dashboard milestone index by scoping IssuesFinder to user authorized projects

username-removed-378947 requested to merge speed-up-dashboard-milestone-index into master

What does this MR do?

It improves performance in dashboard milestone index page by passing a hint to IssuesFinder. IssuesFinder generates a more performant query when it is limited to authorized projects for user. In the dashboard we already limit the projects to these authorized for user (see Dashboard::ApplicationController#projects), so we can safely pass this option to IssuesFinder.

Versions affected

8.13, 8.14. 8.15, 8.16

Why was this MR needed?

Loading dashboard 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

   (1889.2ms)  SELECT COUNT(*) AS count_all, "issues"."state" AS issues_state FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" A
ND "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 "projects"."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 "project_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_features"."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_features ON projects.id = project_features.project_id WHERE (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_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" IN (16, 21, 26, 31)  ORDER BY "milestones"."id" DESC) GROUP BY "issues"."state"  [["pending_delete", "f"], ["pending_delete", "f"]]

After

(6.7ms)  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 INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" 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 "projects"."id" DESC)))) AND "projects"."pending_delete" = $2 AND "project_authorizations"."user_id" = $3 AND "projects"."id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 10 AND ("project_features"."issues_access_level" = 20 OR "project_features"."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_features ON projects.id = project_features.project_id WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 10 AND "project_features"."issues_access_level" = 10) AND "issues"."milestone_id" IN (SELECT "milestones"."id" FROM "milestones" WHERE "milestones"."id" IN (16, 21, 26, 31)  ORDER BY "milestones"."id" DESC) GROUP BY "issues"."state"  [["pending_delete", "f"], ["pending_delete", "f"], ["user_id", 10]]

Does this MR meet the acceptance criteria?

What are the relevant issue numbers?

Merge request reports