Skip to content

Fix performance regression in issuable lists

Heinrich Lee Yurequested to merge
365522-fix-group-issue-list-performance into master

What does this MR do and why?

Reverts part of !89051 (merged) and make the intent of the code clearer.

This fixes a performance regression described in #365522 (closed)

Old query:

SELECT 1 AS one
FROM "issues"
  INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
WHERE "issues"."author_id" NOT IN (SELECT "banned_users"."user_id" FROM "banned_users")
  AND ( issues.confidential IS NOT TRUE OR (issues.confidential = TRUE AND (issues.author_id = 8039735 OR EXISTS (SELECT TRUE FROM issue_assignees WHERE user_id = 8039735 AND issue_id = issues.id) OR EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 8039735 AND (project_authorizations.project_id = issues.project_id) AND (project_authorizations.access_level >= 20)))))
  AND "issues"."project_id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."namespace_id" IN (
    SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{10360152}'))
  )
  AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 8039735 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)) OR projects.visibility_level IN (10,20))
  AND ("project_features"."issues_access_level" IS NULL OR "project_features"."issues_access_level" IN (20,30) OR ("project_features"."issues_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 8039735 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= 10)))))
  AND "projects"."archived" = FALSE
LIMIT 1;
Limit  (cost=258.20..45275.05 rows=1 width=4)
  ->  Nested Loop  (cost=258.20..747550179.23 rows=16606 width=4)
        ->  Merge Semi Join  (cost=257.76..747541533.63 rows=17014 width=8)
              Merge Cond: (issues.project_id = projects_1.id)
              ->  Index Scan using index_issues_on_project_id_and_iid on issues  (cost=5.65..482654277.36 rows=31964653 width=4)
                    Filter: ((NOT (hashed SubPlan 1)) AND ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 8039735) OR (alternatives: SubPlan 2 or hashed SubPlan 3) OR (alternatives: SubPlan 4 or hashed SubPlan 5)))))
                    SubPlan 1
                      ->  Index Only Scan using banned_users_pkey on banned_users  (cost=0.14..4.81 rows=111 width=8)
                    SubPlan 2
                      ->  Index Only Scan using issue_assignees_pkey on issue_assignees  (cost=0.56..3.58 rows=1 width=0)
                            Index Cond: ((issue_id = issues.id) AND (user_id = 8039735))
                    SubPlan 3
                      ->  Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1  (cost=0.56..94.16 rows=107 width=4)
                            Index Cond: (user_id = 8039735)
                    SubPlan 4
                      ->  Index Only Scan using project_authorizations_pkey on project_authorizations  (cost=0.57..3.60 rows=1 width=0)
                            Index Cond: ((user_id = 8039735) AND (project_id = issues.project_id) AND (access_level >= 20))
                    SubPlan 5
                      ->  Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_1  (cost=0.57..904.40 rows=5181 width=4)
                            Index Cond: ((user_id = 8039735) AND (access_level >= 20))
              ->  Nested Loop Left Join  (cost=252.11..264807140.87 rows=13455 width=4)
                    Filter: ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.issues_access_level = 10) AND (alternatives: SubPlan 8 or hashed SubPlan 9)))
                    ->  Nested Loop Semi Join  (cost=251.54..264767670.24 rows=8499 width=4)
                          Join Filter: (projects_1.namespace_id = namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)])
                          ->  Index Scan using projects_pkey on projects projects_1  (cost=0.44..93426616.51 rows=14356072 width=8)
                                Filter: ((alternatives: SubPlan 6 or hashed SubPlan 7) OR (visibility_level = ANY ('{10,20}'::integer[])))
                                SubPlan 6
                                  ->  Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_2  (cost=0.57..3.60 rows=1 width=0)
                                        Index Cond: ((user_id = 8039735) AND (project_id = projects_1.id) AND (access_level >= 10))
                                SubPlan 7
                                  ->  Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_3  (cost=0.57..1087.98 rows=6370 width=4)
                                        Index Cond: ((user_id = 8039735) AND (access_level >= 10))
                          ->  Materialize  (cost=251.11..1336.12 rows=682 width=28)
                                ->  Bitmap Heap Scan on namespaces  (cost=251.11..1332.71 rows=682 width=28)
                                      Recheck Cond: ((traversal_ids @> '{10360152}'::integer[]) AND ((type)::text = 'Group'::text))
                                      ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..250.93 rows=682 width=0)
                                            Index Cond: (traversal_ids @> '{10360152}'::integer[])
                    ->  Index Scan using index_project_features_on_project_id_include_container_registry on project_features  (cost=0.56..1.03 rows=1 width=8)
                          Index Cond: (project_id = projects_1.id)
                    SubPlan 8
                      ->  Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_4  (cost=0.57..3.60 rows=1 width=0)
                            Index Cond: ((user_id = 8039735) AND (project_id = project_features.project_id) AND (access_level >= 10))
                    SubPlan 9
                      ->  Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_5  (cost=0.57..1087.98 rows=6370 width=4)
                            Index Cond: ((user_id = 8039735) AND (access_level >= 10))
        ->  Index Scan using projects_pkey on projects  (cost=0.44..0.51 rows=1 width=4)
              Index Cond: (id = issues.project_id)
              Filter: (NOT archived)

(Can't get execution plan for this because it's not returning even after 1hr+)

New query:

SELECT 1 AS one
FROM "issues"
  INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
  LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "issues"."author_id" NOT IN (SELECT "banned_users"."user_id" FROM "banned_users")
  AND ( issues.confidential IS NOT TRUE OR (issues.confidential = TRUE AND (issues.author_id = 8039735 OR EXISTS (SELECT TRUE FROM issue_assignees WHERE user_id = 8039735 AND issue_id = issues.id) OR EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 8039735 AND (project_authorizations.project_id = issues.project_id) AND (project_authorizations.access_level >= 20)))))
  AND "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{10360152}')))
  AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 8039735 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)) OR projects.visibility_level IN (10,20))
  AND ("project_features"."issues_access_level" IS NULL OR "project_features"."issues_access_level" IN (20,30) OR ("project_features"."issues_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 8039735 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= 10))))
  AND "projects"."archived" = FALSE
LIMIT 1;
Time: 10.974 ms
  - planning: 3.147 ms
  - execution: 7.827 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 966 (~7.50 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/10731/commands/38695

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 #365522 (closed)

Edited by Heinrich Lee Yu

Merge request reports