Fix Projects list API preloaders
What does this MR do and why?
A query of the /api/v4/projects
endpoint renders data from a lot of models that are associated with the projects. To avoid N+1
issues the associations are preloaded in various ways. These preloaders are meant to do the equivalent of:
projects = Project.where(...)
project_ids = projects.map(&:id)
preloaded_issues = Issue.where(project_id: project_ids)
# select * from issues where project_id IN (1,2,3,4)
But instead some "preloaders" are doing this:
projects = Project.where(...)
issues = Issue.where(project_id: projects)
# select * from issues where project_id IN (select id from projects where ...)
It was uncovered that some of these preloader queries are not very fast.
This MR corrects a couple of preloaders to replace the subselect IN (select id ...)
with a much faster IN (1, 2, 3)
form.
This work is completed behind the projects_preloader_fix
feature flag.
Query Plans
Old
-- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12805/commands/44952
SELECT
"projects".*,
"project_authorizations"."user_id",
"project_authorizations"."access_level"
FROM
"projects"
LEFT OUTER JOIN "project_authorizations" AS project_authorizations ON "project_authorizations"."project_id" = "projects"."id"
AND "project_authorizations"."user_id" = 4092171
WHERE
"projects"."id" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
(
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 4092171
AND (
project_authorizations.project_id = projects.id
)
)
OR projects.visibility_level IN (10, 20)
)
AND "projects"."pending_delete" = FALSE
AND "projects"."hidden" = FALSE
ORDER BY
"projects"."id" DESC
LIMIT
20 OFFSET 0
)
-- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12805/commands/44954
SELECT
"members".*
FROM
"members"
WHERE
"members"."source_type" = 'Project'
AND "members"."type" = 'ProjectMember'
AND "members"."user_id" = 4092171
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
(
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 4092171
AND (
project_authorizations.project_id = projects.id
)
)
OR projects.visibility_level IN (10, 20)
)
AND "projects"."pending_delete" = FALSE
AND "projects"."hidden" = FALSE
ORDER BY
"projects"."id" DESC
LIMIT
20 OFFSET 0
)
New
-- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12805/commands/44961
SELECT
"projects".*,
"project_authorizations"."user_id",
"project_authorizations"."access_level"
FROM
"projects"
LEFT OUTER JOIN "project_authorizations" AS project_authorizations ON "project_authorizations"."project_id" = "projects"."id"
AND "project_authorizations"."user_id" = 4092171
WHERE
"projects"."id" IN (40495644, 40495640, 40495630, 40495608, 40495603, 40495588, 40495587, 40495586, 40495585, 40495573, 40495571, 40495562, 40495539, 40495538, 40495530, 40495489, 40495486, 40495480, 40495479, 40495475)
-- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12805/commands/44963
SELECT
"members".*
FROM
"members"
WHERE
"members"."source_type" = 'Project'
AND "members"."type" = 'ProjectMember'
AND "members"."user_id" = 4092171
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN (40495644, 40495640, 40495630, 40495608, 40495603, 40495588, 40495587, 40495586, 40495585, 40495573, 40495571, 40495562, 40495539, 40495538, 40495530, 40495489, 40495486, 40495480, 40495479, 40495475
)
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #375549 (closed)