Skip to content

Draft: Search for N+1 Queries

mo khan requested to merge mokhax/479316/project-authorizations-worker into master

What does this MR do and why?

This MR attempts to find any N+1 queries in the AuthorizedProjectsWorker.

SELECT "users".* FROM find_users_by_id(2) AS users WHERE ("users"."id" IS NOT NULL) LIMIT 1 /*application:test,correlation_id:f395c142200f250fee5c28a069833d64,db_config_database:gitlabhq_test,db_config_name:main,line:/app/workers/authorized_projects_worker.rb:20:in `perform'*/

SELECT "project_authorizations"."project_id", "project_authorizations"."access_level" FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 2 /*application:test,correlation_id:f395c142200f250fee5c28a069833d64,db_config_database:gitlabhq_test,db_config_name:main,line:/app/services/authorized_project_update/find_records_due_for_refresh_service.rb:76:in `current_authorizations_per_project'*/

WITH RECURSIVE "namespaces_cte" AS ((SELECT "namespaces"."id", "members"."access_level" FROM "namespaces" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "namespaces"."type" = 'Group' AND "members"."user_id" = 2 AND "members"."requested_at" IS NULL AND (access_level >= 10) AND "members"."state" = 0)
UNION
(SELECT "namespaces"."id", LEAST("members"."access_level", "group_group_links"."group_access") AS access_level FROM "namespaces" INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id" INNER JOIN "members" ON "group_group_links"."shared_with_group_id" = "members"."source_id" AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."user_id" = 2 AND "members"."state" = 0 AND "members"."access_level" > 5 WHERE "namespaces"."type" = 'Group')
UNION
(SELECT "namespaces"."id", GREATEST("members"."access_level", "namespaces_cte"."access_level") AS access_level FROM "namespaces" INNER JOIN "namespaces_cte" ON "namespaces_cte"."id" = "namespaces"."parent_id" LEFT OUTER JOIN "members" ON "members"."source_id" = "namespaces"."id" AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."user_id" = 2 AND "members"."state" = 0 AND "members"."access_level" > 5 WHERE "namespaces"."type" = 'Group')) SELECT "project_authorizations"."project_id", MAX(access_level) AS access_level FROM ((SELECT projects.id AS project_id, "members"."access_level" FROM "projects" INNER JOIN "members" ON "projects"."id" = "members"."source_id" WHERE "members"."type" = 'ProjectMember' AND "members"."source_type" = 'Project' AND "members"."user_id" = 2 AND "members"."requested_at" IS NULL AND "members"."state" = 0)
UNION
(SELECT projects.id AS project_id, 50 AS access_level FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" WHERE "namespaces"."owner_id" = 2 AND "namespaces"."type" = 'User')
UNION
(SELECT "projects"."id" AS project_id, "namespaces"."access_level" FROM "namespaces_cte" "namespaces" INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id")
UNION
(SELECT "project_group_links"."project_id", LEAST("namespaces"."access_level", "project_group_links"."group_access") AS access_level FROM "namespaces_cte" "namespaces" INNER JOIN project_group_links ON project_group_links.group_id = namespaces.id INNER JOIN projects ON projects.id = project_group_links.project_id INNER JOIN namespaces p_ns ON p_ns.id = projects.namespace_id WHERE (p_ns.share_with_group_lock IS FALSE))) project_authorizations GROUP BY "project_authorizations"."project_id" /*application:test,correlation_id:f395c142200f250fee5c28a069833d64,db_config_database:gitlabhq_test,db_config_name:main,line:/app/services/authorized_project_update/find_records_due_for_refresh_service.rb:70:in `each_with_object'*/

SELECT "users".* FROM find_users_by_id(2) AS users WHERE ("users"."id" IS NOT NULL) LIMIT 1 /*application:test,correlation_id:f395c142200f250fee5c28a069833d64,db_config_database:gitlabhq_test,db_config_name:main,line:/app/services/users/refresh_authorized_projects_service.rb:88:in `update_authorizations'*/

Before:

WITH RECURSIVE "namespaces_cte" AS (
  (
    SELECT "namespaces"."id", "members"."access_level"
    FROM "namespaces"
    INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
    WHERE "members"."type" = 'GroupMember'
      AND "members"."source_type" = 'Namespace'
      AND "namespaces"."type" = 'Group'
      AND "members"."user_id" = 1
      AND "members"."requested_at" IS NULL
      AND (access_level >= 10)
      AND "members"."state" = 0
  )
  UNION
  (
    SELECT "namespaces"."id", LEAST("members"."access_level", "group_group_links"."group_access") AS access_level
    FROM "namespaces"
      INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id"
      INNER JOIN "members" ON "group_group_links"."shared_with_group_id" = "members"."source_id"
      AND "members"."source_type" = 'Namespace'
      AND "members"."requested_at" IS NULL
      AND "members"."user_id" = 1
      AND "members"."state" = 0
      AND "members"."access_level" > 5
    WHERE "namespaces"."type" = 'Group'
  )
  UNION
  (
    SELECT "namespaces"."id", GREATEST("members"."access_level", "namespaces_cte"."access_level") AS access_level
    FROM "namespaces"
      INNER JOIN "namespaces_cte" ON "namespaces_cte"."id" = "namespaces"."parent_id"
      LEFT OUTER JOIN "members" ON "members"."source_id" = "namespaces"."id"
      AND "members"."source_type" = 'Namespace'
      AND "members"."requested_at" IS NULL
      AND "members"."user_id" = 1
      AND "members"."state" = 0
      AND "members"."access_level" > 5
    WHERE "namespaces"."type" = 'Group'
  )
)
SELECT "project_authorizations"."project_id", MAX(access_level) AS access_level
FROM
  (
    (
      SELECT projects.id AS project_id, "members"."access_level"
      FROM "projects"
      INNER JOIN "members" ON "projects"."id" = "members"."source_id"
      WHERE "members"."type" = 'ProjectMember'
        AND "members"."source_type" = 'Project'
        AND "members"."user_id" = 1
        AND "members"."requested_at" IS NULL
        AND "members"."state" = 0
    )
    UNION
    (
      SELECT projects.id AS project_id, 50 AS access_level
      FROM "projects"
      INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id"
      WHERE "namespaces"."owner_id" = 1
        AND "namespaces"."type" = 'User'
    )
    UNION
    (
      SELECT "projects"."id" AS project_id, "namespaces"."access_level"
      FROM "namespaces_cte" "namespaces"
      INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
    )
    UNION
    (
      SELECT "project_group_links"."project_id", LEAST("namespaces"."access_level", "project_group_links"."group_access") AS access_level
      FROM "namespaces_cte" "namespaces"
      INNER JOIN project_group_links ON project_group_links.group_id = namespaces.id
      INNER JOIN projects ON projects.id = project_group_links.project_id
      INNER JOIN namespaces p_ns ON p_ns.id = projects.namespace_id
      WHERE (p_ns.share_with_group_lock IS FALSE)
    )
  ) project_authorizations
GROUP BY "project_authorizations"."project_id"
Time: 85.186 ms
  - planning: 11.017 ms
  - execution: 74.169 ms
    - I/O read: 5.422 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 74138 (~579.20 MiB) from the buffer pool
  - reads: 4 (~32.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/33061/commands/101933

References

Please include cross links to any resources that are relevant to this MR This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Before After

How to set up and validate locally

Edited by mo khan

Merge request reports