workspaceAutocompleteUsersSearch ~18s CPU-bound: duoStatus flow-trigger preload fan-out for Duo-enabled nested groups

Summary

The GraphQL operation workspaceAutocompleteUsersSearch (used for @mention / assignee / reviewer autocomplete pickers) is extremely slow — ~18s average, mostly CPU-bound — for a GitLab Dedicated customer that recently enabled Duo across several groups with a large, deeply-nested group/project hierarchy. On gitlab.com the same operation is typically < 1s.

A sampled slow request showed 28.9s CPU, ~14M allocated objects, ~924MB allocated, but only ~4.9s DB time — consistent with Ruby materializing many rows rather than a single slow SQL query. Operation count stayed flat before/after Duo enablement, so this is a per-request cost amplification, not a traffic increase.

Root cause (confirmed against gitlab-org/gitlab master)

When the autocomplete query asks for a user's Duo status, the resolver preloads every flow trigger for each returned service account and then filters down to the current project in Ruby.

  1. The field that triggers it — workspaceAutocompleteUsersSearch requests duoStatus { flowTriggerEvents } (via the AutocompleteUserStatus → UserDisabledForDuoUsage fragment).

  2. Unscoped preload of all triggers — ee/app/graphql/ee/resolvers/autocomplete_users_resolver.rb sets preload_flow_triggers: true whenever duo_status is requested, and ee/app/finders/ee/autocomplete/users_finder.rb then preloads ai_flow_triggers and child_item_consumers_flow_triggers for each of the ~20 autocomplete users. Both associations are unscoped — they load every trigger for the service account across every project.

  3. Filtered to the current project in Ruby — ee/app/graphql/types/users/duo_status_type.rb:

    triggers = (object.ai_flow_triggers + object.child_item_consumers_flow_triggers).uniq.select(&:active?)
# ...
when ::Project
  triggers.select { |trigger| trigger.project_id == parent_entity.id }

    It materializes the full set, then discards all but one project's triggers in memory.

  4. Cost grows with the number of Duo-enabled projects — child_item_consumers_flow_triggers goes service_account → ai_catalog_item_consumers → child_item_consumers → flow_trigger. Because each item consumer is sharded to exactly one project (sharding_keys = [:group, :project]), enabling a foundational flow per-project creates a separate consumer + trigger in every project. So a service account enabled across a large nested hierarchy accumulates one trigger per project — exactly matching the observed CPU/allocation blow-up.

This also explains why slowness persists after Namespaces::CascadeDuoSettingsWorker finishes (the fan-out is in the read path, not the cascade) and why the operation count didn't change.

Notes

  • This query runs per-project (namespace: project(fullPath:)), so parent_entity is a Project, not a Namespace — the group project_ids branch of duo_status_type.rb is not the driver here.
  • MR !257558 touches these same files but does not fix this. It adds group-inheritance lookups (inherited_triggers) behind the ai_catalog_group_inherited_consumers flag and leaves the slow path untouched — preload_flow_triggers still preloads both full associations and filtered_triggers still does the same in-Ruby select by project_id.

Suggested fix direction

Scope the preload before loading instead of load-then-filter-in-Ruby:

  • Constrain ai_flow_triggers / child_item_consumers_flow_triggers to the current project's triggers.
  • Limit to service accounts that actually need the Duo badge.

A quick confirmation experiment: replay the slow search with preload_flow_triggers: true vs false and compare allocations/CPU.

Impact

  • GitLab Dedicated customer with a large nested hierarchy: ~18s average, CPU-bound, on a core collaboration UI (mentions / assignee / reviewer pickers).
  • Any instance with Duo enabled across many projects could hit the same amplification.

/cc @jsoltani — flagged for prioritization per team request.

Raised from an internal Slack discussion; customer-specific details are in an internal note on this issue.