Draft: Update finder to optimize the performance of searching a root group
What does this MR do and why?
The usage events GraphQL query is timing out for larger root groups, such as gitlab-org
. Changing the approach to search by_root_id
instead is significantly faster:
Metric |
Query 1 self_and_descendants` |
by_root_id |
Improvement |
---|---|---|---|
Total Time | 7.132s | 205.6ms | 97% faster |
Execution Time | 7.126s | 200.3ms | 97% faster |
I/O Read Time | 1.439s | 119.6ms | 92% faster |
Disk Reads | 8,023 pages (62.7MB) | 50 pages (400KB) | 99% less I/O |
This doesn't solve potential issues with deep subgroups.
References
GraphQL
GraphQL query
{
group(fullPath: "gitlab-org") {
aiUsageData {
all {
nodes {
id
timestamp
user {
id
username
}
event
}
pageInfo {
endCursor
hasNextPage
}
}
}
}
}
GraphQL output
{
"data": {
"group": {
"aiUsageData": {
"all": {
"nodes": [
{
"id": "gid://gitlab/Ai::UsageEvent/2",
"timestamp": "2025-06-30T11:21:56Z",
"user": {
"id": "gid://gitlab/User/1",
"username": "root"
},
"event": "TROUBLESHOOT_JOB"
},
{
"id": "gid://gitlab/Ai::UsageEvent/1",
"timestamp": "2025-07-01T11:21:51Z",
"user": {
"id": "gid://gitlab/User/1",
"username": "root"
},
"event": "TROUBLESHOOT_JOB"
}
],
"pageInfo": {
"endCursor": "eyJpZCI6IjEifQ",
"hasNextPage": false
}
}
}
}
},
"correlationId": "01JZAVZDHHVWSPWZBJZYSB2GA0"
}
Query plans
Query plan before: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41714/commands/128051
Query plan after: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41714/commands/128052
How to set up and validate locally
Note: If you haven't set up Duo enterprise locally, you can apply this patch to override the check:
Index: ee/app/models/ee/user.rb
===================================================================
diff --git a/ee/app/models/ee/user.rb b/ee/app/models/ee/user.rb
--- a/ee/app/models/ee/user.rb (revision Staged)
+++ b/ee/app/models/ee/user.rb (date 1751637798349)
@@ -528,6 +528,8 @@
end
def assigned_to_duo_enterprise?(container)
+ return true
+
namespace = ::Gitlab::Saas.feature_available?(:gitlab_duo_saas_only) ? container.root_ancestor : nil
GitlabSubscriptions::AddOnPurchase
- In rails console enable the feature flag
:unified_ai_events_graphql
via http://gdk.test:3000/rails/features - Visit the GraphQL explorer: http://gdk.test:3000/-/graphql-explorer
- Enter the GraphQL query above and confirm the output
- If you don't have any usage events, you should be able to populate by running
FILTER=ai_usage_stats bundle exec rake db:seed_fu
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.