Resolve "Slow SQL running in production queryid 3257994922"
What does this MR do?
Previous query
SELECT "projects".*
FROM "projects"
INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE (
namespace_id IN (
SELECT "namespaces"."id"
FROM "namespaces"
INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
WHERE "members"."source_type" = 'Namespace'
AND "namespaces"."type" IN ('Group')
AND "members"."user_id" = 1
AND "namespaces"."type" IN ('Group')
AND "members"."access_level" = 50
AND "members"."type" IN ('GroupMember')
AND "members"."requested_at" IS NULL
)
OR namespace_id = 2
)
New query
SELECT
"projects".*
FROM (SELECT
"projects".*
FROM "projects"
WHERE "projects"."namespace_id" = 752513
UNION ALL
SELECT
"projects".*
FROM "projects"
INNER JOIN "project_authorizations"
ON "project_authorizations"."project_id" = "projects"."id"
WHERE (projects.namespace_id <> 752513)
AND "project_authorizations"."user_id" = 632546
AND "project_authorizations"."access_level" = 50) AS projects
Improves the performance of the query in https://gitlab.com/gitlab-org/gitlab-ce/blob/master/app/models/user.rb#L625
Does this MR meet the acceptance criteria?
-
Changelog entry added, if necessary -
Tests added for this feature/bug - Review
-
Has been reviewed by Backend -
Has been reviewed by Database
-
-
Conform by the merge request performance guides -
Conform by the style guides -
Squashed related commits together
What are the relevant issue numbers?
Closes #43552 (closed)
Edited by Tiago Botelho