Fix performance regression in issuable lists
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.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #365522 (closed)
Edited by Heinrich Lee Yu