Rewrite SQL query for ByApproversFinder

What

Rewrite SQL query for ByApproversFinder

Why

Now the query doesn't perform cross-database joins for users and namespaces.

Related issue: Cells: Fix cross joins related approval_state/a... (#417459 - closed)

Queries

Before:

SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."id" IN (SELECT "merge_requests"."id" FROM ((SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" INNER JOIN "approval_merge_request_rules_users" ON "approval_merge_request_rules_users"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id" INNER JOIN "users" ON "users"."id" = "approval_merge_request_rules_users"."user_id" WHERE "users"."username" = 'root' GROUP BY "merge_requests"."id" HAVING (COUNT(users.id) = 1))
UNION
(SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" INNER JOIN "approval_merge_request_rules_groups" ON "approval_merge_request_rules_groups"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id" INNER JOIN "namespaces" ON "namespaces"."id" = "approval_merge_request_rules_groups"."group_id" AND "namespaces"."type" = 'Group' INNER JOIN "members" ON "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."access_level" != 5 AND "members"."source_id" = "namespaces"."id" AND "members"."type" = 'GroupMember' INNER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "users"."username" = 'root' GROUP BY "merge_requests"."id" HAVING (COUNT(users.id) = 1))
UNION
(SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id" INNER JOIN "approval_project_rules_users" ON "approval_project_rules_users"."approval_project_rule_id" = "approval_project_rules"."id" INNER JOIN "users" ON "users"."id" = "approval_project_rules_users"."user_id" LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" WHERE "approval_merge_request_rules"."id" IS NULL AND "users"."username" = 'root' GROUP BY "merge_requests"."id" HAVING (COUNT(users.id) = 1))
UNION
(SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id" INNER JOIN "approval_project_rules_groups" ON "approval_project_rules_groups"."approval_project_rule_id" = "approval_project_rules"."id" INNER JOIN "namespaces" ON "namespaces"."id" = "approval_project_rules_groups"."group_id" AND "namespaces"."type" = 'Group' INNER JOIN "members" ON "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."access_level" != 5 AND "members"."source_id" = "namespaces"."id" AND "members"."type" = 'GroupMember' INNER JOIN "users" ON "users"."id" = "members"."user_id" LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" WHERE "approval_merge_request_rules"."id" IS NULL AND "users"."username" = 'root' GROUP BY "merge_requests"."id" HAVING (COUNT(users.id) = 1))) merge_requests)

Now:

SELECT "users"."id" FROM "users" WHERE "users"."username" = 'root'

SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."id" IN (SELECT "merge_requests"."id" FROM ((SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" INNER JOIN "approval_merge_request_rules_users" ON "approval_merge_request_rules_users"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id" WHERE "approval_merge_request_rules_users"."user_id" = 1 GROUP BY "merge_requests"."id" HAVING (COUNT(approval_merge_request_rules_users.user_id) = 1))
UNION
(SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" INNER JOIN "approval_merge_request_rules_groups" ON "approval_merge_request_rules_groups"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id" INNER JOIN "namespaces" ON "namespaces"."id" = "approval_merge_request_rules_groups"."group_id" AND "namespaces"."type" = 'Group' INNER JOIN "members" ON "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."access_level" != 5 AND "members"."source_id" = "namespaces"."id" AND "members"."type" = 'GroupMember' WHERE "members"."user_id" = 1 GROUP BY "merge_requests"."id" HAVING (COUNT(members.user_id) = 1))
UNION
(SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id" INNER JOIN "approval_project_rules_users" ON "approval_project_rules_users"."approval_project_rule_id" = "approval_project_rules"."id" LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" WHERE "approval_merge_request_rules"."id" IS NULL AND "approval_project_rules_users"."user_id" = 1 GROUP BY "merge_requests"."id" HAVING (COUNT(approval_project_rules_users.user_id) = 1))
UNION
(SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id" INNER JOIN "approval_project_rules_groups" ON "approval_project_rules_groups"."approval_project_rule_id" = "approval_project_rules"."id" INNER JOIN "namespaces" ON "namespaces"."id" = "approval_project_rules_groups"."group_id" AND "namespaces"."type" = 'Group' INNER JOIN "members" ON "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."access_level" != 5 AND "members"."source_id" = "namespaces"."id" AND "members"."type" = 'GroupMember' LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" WHERE "approval_merge_request_rules"."id" IS NULL AND "members"."user_id" = 1 GROUP BY "merge_requests"."id" HAVING (COUNT(members.user_id) = 1))) merge_requests)

However, the query is extremely slow and I've created an issue to discuss what can we do about it: https://gitlab.com/gitlab-org/gitlab/-/issues/431506+ and shared query plans there. This MR removes a couple of joins, so the changes shouldn't make the current query worse (according to the query plans). But since this MR is about removing cross-join, optimizing it to an acceptable level is out-of-scope of this issue.