DashboardController#issues is slow due to SQL

This actually ties into the IssuesFinder and filtering of issues. Here's one of the ugly SQL queries that results in ~11 s of load time:

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" = 'f' WHERE "issues"."deleted_at" IS NULL AND (
      issues.confidential IS NULL
      OR issues.confidential IS FALSE
      OR (issues.confidential = TRUE
        AND (issues.author_id = 64248
          OR issues.assignee_id = 64248
          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" = 64248 AND (project_authorizations.access_level >= 20)  ORDER BY "projects"."id" DESC)))) 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" = 64248
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" = 64248
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" = 64248
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "project_authorizations"."user_id" = 64248 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" = 64248
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "project_features"."issues_access_level" = 10) AND "projects"."archived" = 'f' GROUP BY "issues"."state" 

EXPLAIN ANALYZE: https://explain.depesz.com/s/nRY

The huge JOINs and sequential scans concern me.

It looks like this ties in with the other issues where searching for all authorized_projects may be overkill here: gitlab-org/gitlab-ce#18586

changed milestone to %8.17

Related:

• https://gitlab.com/gitlab-org/gitlab-ce/issues/25504
• https://gitlab.com/gitlab-org/gitlab-ce/issues/25503

@stanhu could we do https://gitlab.com/gitlab-org/gitlab-ce/issues/25503 and then revisit this? I'd like to have one open performance issue per page, so that we can at least bump pages down the list, and pick them up again when they're one of the slowest once more.

@smcgivern Sure, doing #25503 (closed) makes sense first.

removed milestone

mentioned in issue #31204 (moved)

mentioned in issue #30265 (closed)

mentioned in issue gitlab-com/support-forum#1786 (closed)

mentioned in issue #31860 (closed)

mentioned in issue gitlab-com/support-forum#1826 (closed)

mentioned in issue #37806 (closed)

moved to gitlab#8075

Hey!

GitLab is moving all development for both GitLab Community Edition and Enterprise Edition into a single codebase. The current gitlab-ce repository will become a read-only mirror, without any proprietary code. All development is moved to the current gitlab-ee repository, which we will rename to just gitlab in the coming weeks. As part of this migration, issues will be moved to the current gitlab-ee project.

If you have any questions about all of this, please ask them in our dedicated FAQ issue.

https://gitlab.com/gitlab-org/gitlab-ee/issues/13855

You can also find more information here:

• https://gitlab.com/gitlab-org/gitlab-ee/issues/13304
• https://about.gitlab.com/2019/08/23/a-single-codebase-for-gitlab-community-and-enterprise-edition/

Frequently Asked Questions

For an up to date list of questions and answers, please take a look at https://gitlab.com/gitlab-org/gitlab-ee/issues/13855

What will we do with the repository names?

https://gitlab.com/gitlab-org/gitlab-ee/ will become https://gitlab.com/gitlab-org/gitlab, and https://gitlab.com/gitlab-org/gitlab-ce will become https://gitlab.com/gitlab-org/gitlab-foss.

Why rename gitlab-ce to gitlab-foss?

Using "gitlab" and "gitlab-ce" would be confusing, so we decided to rename gitlab-ce to gitlab-foss to make the purpose of this FOSS repository more clear

I created a merge requests for CE, and this got closed. What do I need to do?

You will need to create a merge request at https://gitlab.com/gitlab-org/gitlab-ee/. This link will eventually redirect to https://gitlab.com/gitlab-org/gitlab.

How does the licensing work in this new setup?

Everything in the ee/ directory is proprietary. Everything else is free and open source software. If your merge request does not change anything in the ee/ directory, the process of contributing changes is the same as when using the gitlab-ce repository.

Will you accept merge requests on the gitlab-ce/gitlab-foss project after it has been renamed?

No. Merge requests submitted to this project will be closed automatically.

Will I still be able to view old issues and merge requests in gitlab-ce/gitlab-foss?

Yes.

How will this affect users of GitLab CE using Omnibus?

No changes will be necessary, as the packages built remain the same.

How will this affect users of GitLab CE that build from source?

Once the project has been renamed, you will need to change your Git remotes to use this new URL. GitLab will take care of redirecting Git operations so there is no hard deadline, but we recommend doing this as soon as the projects have been renamed.

Where can I see a timeline of the remaining steps?

https://gitlab.com/gitlab-org/gitlab-ee/issues/13304

Will community contributions submitted to the new "gitlab" repository be available in the new gitlab-foss repository?

Yes, these changes will be synced to gitlab-foss automatically, several times per day.

locked this issue