Skip to content

Fix N+1 calls on Code Suggestions authorisation check

Tan Le requested to merge 412655-n-1-calls-on-code-suggestions-authorisation-check into master

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

  1. 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.

Related to #412655 (closed)

Edited by Tan Le

Merge request reports