N+1 issue when loading labels in search dropdowns
When loading labels in search dropdowns, this query is quite slow, part of the problem is that there is N+1 issue when we load all subgroups and ancestor groups for which we want to list labels - for each group we search for root ancestor group -> because we know that all these groups have the same top-level group, we can optimize this and preset root group for all subgroups before permission checking.
sample query we call:
"sql": "WITH RECURSIVE \"base_and_ancestors\" AS ((SELECT \"namespaces\".* FROM \"namespaces\" WHERE \"namespaces\".\"type\" = 'Group' AND \"namespaces\".\"id\" = 9082402)\nUNION\n(SELECT \"namespaces\".* FROM \"namespaces\", \"base_and_ancestors\" WHERE \"namespaces\".\"type\" = 'Group' AND \"namespaces\".\"id\" = \"base_and_ancestors\".\"parent_id\")) SELECT \"namespaces\".* FROM \"base_and_ancestors\" AS \"namespaces\" WHERE \"namespaces\".\"parent_id\" IS NULL LIMIT 1 /*application:web,controller:labels,action:index,correlation_id:01EWJS844BYTFV9D7TE071GX82*/",
"backtrace": [
"app/models/namespace.rb:319:in `block in root_ancestor'",
"lib/gitlab/utils/strong_memoize.rb:30:in `strong_memoize'",
"app/models/namespace.rb:318:in `root_ancestor'",
"ee/lib/gitlab/auth/group_saml/sso_enforcer.rb:33:in `group_access_restricted?'",
"ee/app/policies/ee/group_policy.rb:352:in `sso_enforcement_prevents_access?'",
"ee/app/policies/ee/group_policy.rb:61:in `block (2 levels) in <module:GroupPolicy>'",
"lib/declarative_policy/condition.rb:23:in `instance_eval'",
"lib/declarative_policy/condition.rb:23:in `compute'",
"lib/declarative_policy/condition.rb:44:in `block in pass?'",
"lib/declarative_policy/base.rb:303:in `cache'",
"lib/declarative_policy/condition.rb:44:in `pass?'",
"lib/declarative_policy/rule.rb:81:in `pass?'",
"lib/declarative_policy/step.rb:81:in `pass?'",
"lib/declarative_policy/runner.rb:101:in `block in run'",
"lib/declarative_policy/runner.rb:180:in `block in steps_by_score'",
"lib/declarative_policy/runner.rb:149:in `loop'",
"lib/declarative_policy/runner.rb:149:in `steps_by_score'",
"lib/declarative_policy/runner.rb:82:in `run'",
"lib/declarative_policy/runner.rb:60:in `pass?'",
"lib/declarative_policy/base.rb:234:in `block in allowed?'",
"lib/declarative_policy/base.rb:234:in `all?'",
"lib/declarative_policy/base.rb:234:in `allowed?'",
"lib/declarative_policy/rule.rb:159:in `pass?'",
"lib/declarative_policy/step.rb:81:in `pass?'",
"lib/declarative_policy/runner.rb:92:in `block in run'",
"lib/declarative_policy/runner.rb:180:in `block in steps_by_score'",
"lib/declarative_policy/runner.rb:149:in `loop'",
"lib/declarative_policy/runner.rb:149:in `steps_by_score'",
"lib/declarative_policy/runner.rb:82:in `run'",
"lib/declarative_policy/runner.rb:60:in `pass?'",
"lib/declarative_policy/base.rb:234:in `block in allowed?'",
"lib/declarative_policy/base.rb:234:in `all?'",
"lib/declarative_policy/base.rb:234:in `allowed?'",
"lib/declarative_policy/base.rb:226:in `can?'",
"app/models/ability.rb:72:in `allowed?'",
"app/finders/labels_finder.rb:193:in `authorized_to_read_labels?'",
"app/finders/labels_finder.rb:198:in `block (2 levels) in groups_user_can_read_labels'",
"app/finders/labels_finder.rb:198:in `select'",
"app/finders/labels_finder.rb:198:in `block in groups_user_can_read_labels'",
"lib/declarative_policy/preferred_scope.rb:9:in `with_preferred_scope'",
"lib/declarative_policy/preferred_scope.rb:19:in `user_scope'",
"app/finders/labels_finder.rb:197:in `groups_user_can_read_labels'",
"app/finders/labels_finder.rb:103:in `block in group_ids_for'",
"lib/gitlab/utils/strong_memoize.rb:30:in `strong_memoize'",
"app/finders/labels_finder.rb:100:in `group_ids_for'",
"app/finders/labels_finder.rb:49:in `label_ids'",
"app/finders/labels_finder.rb:17:in `execute'",
"app/finders/concerns/finder_with_cross_project_access.rb:37:in `block in execute'",
"app/finders/concerns/finder_with_cross_project_access.rb:40:in `execute'",
"app/controllers/groups/labels_controller.rb:118:in `available_labels'",
"app/controllers/groups/labels_controller.rb:22:in `block (2 levels) in index'",
"app/controllers/groups/labels_controller.rb:15:in `index'",
Edited by Jan Provaznik