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

#552360 (comment 2641738681)

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"
}

Pagination

Scenario	Result
Default 100 limit
Overridden limit to 1
Overridden limit to 1 next page

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

1. In rails console enable the feature flag :unified_ai_events_graphql via http://gdk.test:3000/rails/features
2. Visit the GraphQL explorer: http://gdk.test:3000/-/graphql-explorer
3. Enter the GraphQL query above and confirm the output
4. 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.