Skip to content

Fix Projects list API preloaders

Alex Pooley requested to merge 368879-projects-list-api-slow-param-search into master

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.

Related to #375549 (closed)

Edited by Alex Pooley

Merge request reports