Add support for filtering by group/project in runner usage queries

What does this MR do and why?

Add support for filtering by group/project in runner usage queries

• Changelog: added
• EE: true

Closes #440605 (closed)

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

1. Ensure you have an Ultimate license associated with your GDK

2. Follow https://docs.gitlab.com/ee/development/database/clickhouse/clickhouse_within_gitlab.html#gdk-setup up to and including running migrations

3. Go to the shell in your GDK gitlab directory and run bundle exec rake "gitlab:seed:runner_fleet". This will seed your GDK with some runners and jobs required for testing this MR.

4. Run the following in the rails console:

   ::Ci::Build.where.not(finished_at: nil).order(id: :desc).limit(5000).each{|build| ::Ci::FinishedBuildChSyncEvent.upsert({ build_id: build.id, build_finished_at: build.finished_at }, unique_by: [:build_id, :partition]) }
   ClickHouse::DataIngestion::CiFinishedBuildsSyncService.new.execute

5. visit -/graphql-explorer execute the following graphql query, replacing the fullPathD with the value returned by Ci::Runner.group_type.last.groups.first.full_path:

   {
     runnerUsageByProject(
       fullPath: "rf-top-level-group-1"
       fromDate: "2023-01-01"
       toDate: "2023-12-31"
       projectsLimit: 10
     ) {
       project {
         id
         fullPath
       }
       ciBuildCount
       ciMinutesUsed
     }
   }

You should get something like:

{
  "data": {
    "runnerUsageByProject": [
      {
        "project": {
          "id": "gid://gitlab/Project/20",
          "fullPath": "rf-top-level-group-1/rf-group-1.1/rf-group-1.1.1/rf-project-1-1-1-1"
        },
        "ciBuildCount": "958440",
        "ciMinutesUsed": "521908396"
      },
      {
        "project": {
          "id": "gid://gitlab/Project/21",
          "fullPath": "rf-top-level-group-1/rf-group-1.1/rf-group-1.1.2/rf-project-1-1-2-1"
        },
        "ciBuildCount": "27231",
        "ciMinutesUsed": "14222261"
      }
    ]
  }
}

Database query plan

Worst case scenario of getting all projects under gitlab-org (4126 projects)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/28659/commands/89278

SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN (
    SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
    FROM "namespaces"
    WHERE "namespaces"."type" = 'Group'
      AND (traversal_ids @> ('{9970}')))
LIMIT 5000

 Limit  (cost=871.02..1548.82 rows=5000 width=4) (actual time=1174.825..2563.545 rows=4216 loops=1)
   Buffers: shared hit=6368 read=2692 dirtied=240
   I/O Timings: read=1512.023 write=0.000
   ->  Nested Loop  (cost=871.02..2255.37 rows=10212 width=4) (actual time=1174.823..2562.696 rows=4216 loops=1)
         Buffers: shared hit=6368 read=2692 dirtied=240
         I/O Timings: read=1512.023 write=0.000
         ->  HashAggregate  (cost=870.45..874.35 rows=390 width=28) (actual time=1169.018..1170.379 rows=912 loops=1)
               Group Key: namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)]
               Buffers: shared hit=1 read=1016 dirtied=6
               I/O Timings: read=151.378 write=0.000
               ->  Bitmap Heap Scan on public.namespaces  (cost=261.39..869.47 rows=390 width=28) (actual time=129.737..1164.689 rows=912 loops=1)
                     Buffers: shared hit=1 read=1016 dirtied=6
                     I/O Timings: read=151.378 write=0.000
                     ->  Bitmap Index Scan using index_namespaces_on_traversal_ids_for_groups  (cost=0.00..261.29 rows=390 width=0) (actual time=126.754..126.755 rows=912 loops=1)
                           Index Cond: (namespaces.traversal_ids @> '{9970}'::integer[])
                           Buffers: shared hit=1 read=172
                           I/O Timings: read=122.696 write=0.000
         ->  Index Only Scan using index_projects_on_namespace_id_and_id on public.projects  (cost=0.57..3.28 rows=26 width=8) (actual time=0.940..1.523 rows=5 loops=912)
               Index Cond: (projects.namespace_id = (namespaces.traversal_ids)[array_length(namespaces.traversal_ids, 1)])
               Heap Fetches: 997
               Buffers: shared hit=6367 read=1676 dirtied=234
               I/O Timings: read=1360.645 write=0.000

ClickHouse query

WITH top_buckets AS (
  SELECT project_id AS project_id_bucket
  FROM ci_used_minutes
  WHERE (project_id IN _CAST ([<list of 4126 project IDs>], 'Array(UInt64)'))
  AND (finished_at_bucket >= _CAST (1704067200, 'DateTime(\'UTC\', 6)'))
  AND (finished_at_bucket < _CAST (1735689600, 'DateTime(\'UTC\', 6)'))
GROUP BY project_id
ORDER BY sumSimpleState (total_duration) DESC
LIMIT _CAST (3, 'UInt64'))
SELECT IF (ci_used_minutes.project_id IN (top_buckets), ci_used_minutes.project_id, NULL) AS project_id_bucket,
  countMerge (count_builds) AS count_builds,
  toUInt64 (sumSimpleState (total_duration) / 60000) AS total_duration_in_mins
FROM ci_used_minutes
WHERE (project_id IN _CAST ([<list of 4126 project IDs>], 'Array(UInt64)')) AND (finished_at_bucket >= _CAST (1704067200,
  'DateTime(\'UTC\', 6)')) AND (finished_at_bucket < _CAST (1735689600, 'DateTime(\'UTC\', 6)'))
GROUP BY project_id_bucket
ORDER BY project_id_bucket IS NULL ASC, total_duration_in_mins DESC, project_id_bucket ASC

Elapsed: 1.653s Read: 70,640,188 rows (2.26 GB)