Fix N+1 calls on Code Suggestions authorisation check
What does this MR do and why?
This MR removes the N+1 queries caused by checking code suggestions settings of all groups of which the user is a member.
This MR will also help to fix a bug where code suggestions for top-level setting does not enforce https://gitlab.com/gitlab-org/gitlab/-/issues/413552.
Old queries
pry(main)> user.can?(:access_code_suggestions)
Group Load (5.7ms) SELECT "namespaces".* 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 "namespaces"."parent_id" IS NULL /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/policies/ee/global_policy.rb:61:in `reject'*/
Feature::FlipperGate Load (1.5ms) SELECT "feature_gates".* FROM "feature_gates" WHERE "feature_gates"."feature_key" = 'ai_assist_flag' /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/lib/feature.rb:237:in `block in current_feature_value'*/
NamespaceSetting Load (0.6ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 27 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
NamespaceSetting Load (0.2ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 168 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
NamespaceSetting Load (0.1ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 31 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
NamespaceSetting Load (0.1ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 70 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
NamespaceSetting Load (0.1ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 22 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
NamespaceSetting Load (0.1ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 25 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
NamespaceSetting Load (0.1ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 35 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
NamespaceSetting Load (0.1ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 29 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
NamespaceSetting Load (0.1ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 181 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
NamespaceSetting Load (0.1ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 186 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
NamespaceSetting Load (0.1ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 182 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
NamespaceSetting Load (0.1ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 33 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/group.rb:80:in `code_suggestions'*/
Namespace Load (0.2ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."owner_id" = 1 AND "namespaces"."type" = 'User' LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/user.rb:49:in `code_suggestions_enabled?'*/
NamespaceSetting Load (0.2ms) SELECT "namespace_settings".* FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = 1 LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey,console_username:tanle,line:/ee/app/models/ee/namespace.rb:104:in `code_suggestions'*/
=> true
New queries
pry(main)> user.can?(:access_code_suggestions)
Group Exists? (0.8ms) SELECT 1 AS one FROM "namespaces" INNER JOIN "namespace_settings" ON "namespace_settings"."namespace_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (SELECT DISTINCT "namespaces".traversal_ids[1] 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 "namespace_settings"."code_suggestions" = FALSE LIMIT 1 /*application:console,db_config_name:main,console_hostname:joey.local,console_username:tanle,line:/ee/app/models/ee/user.rb:550:in `code_suggestions_disabled_by_group?'*/
=> false
🐘 Databases
Query
EXPLAIN
SELECT 1 AS one
FROM "namespaces"
INNER JOIN "namespace_settings" ON "namespace_settings"."namespace_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" IN
(SELECT DISTINCT "namespaces".traversal_ids[1]
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" = 64248
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
AND "namespace_settings"."code_suggestions" = FALSE
LIMIT 1
Summary
Time: 24.612 ms
- planning: 8.429 ms
- execution: 16.183 ms
- I/O read: 13.809 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 125 (~1000.00 KiB) from the buffer pool
- reads: 192 (~1.50 MiB) from the OS file cache, including disk I/O
- dirtied: 7 (~56.00 KiB)
- writes: 0
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/19289/commands/63654
Screenshots or screen recordings
N/A
How to set up and validate locally
- Run the following code in rails console and observe the SQL commands before and after this change
user = User.first user.can?(:access_code_suggestions)
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 #412655 (closed)
Edited by Tan Le