Use traversal_ids index for group runners count

Pedro Pombeiro (OOO from July 6th-17th)requested to merge
pedropombeiro/601877/fix-group-runners-count-timeout into master

What does this MR do and why?

The getGroupRunnersCountEE GraphQL query (the runner count on the group Runners page) times out with a 15s statement timeout for namespaces with large subtrees, returning a 503 to the customer.

Root cause: Ci::Runner.belonging_to_group_or_project_descendants resolves descendant namespaces in ci_namespace_mirrors via the GIN operator traversal_ids @> '{id}'. For a large subtree this forces a bitmap heap scan over tens of thousands of heap blocks. On a cold cache this costs ~46s, exceeding the statement timeout — which is why the failure is intermittent on the ci_replica (warm cache resolves in well under 1s).

This MR routes descendant resolution through the purpose-built index_ci_namespace_mirrors_on_traversal_ids_unnest covering index instead, using the existing contains_traversal_ids prefix form ((traversal_ids[1..N]) IN (...)). This serves the same result set as an index-only scan with ~50x less I/O. usable_from_scope (the all_available membership filter) benefits automatically, as it builds on the same scope.

The change is a logical no-op (identical result sets) and is gated behind the ci_runners_count_traversal_ids_index feature flag (default off), gated on Feature.current_request so the query-plan change is observable on the database dashboards during rollout — the same pattern as ci_runner_partition_pruning.

References

Database

The slow query is the descendant-namespace resolution in ci_namespace_mirrors. Validated on Database Lab against root namespace 4249178 (~104K descendant namespaces).

Descendant-namespace lookup

This is the access-path change at the heart of the fix.

Before — GIN traversal_ids @> (~46s, bitmap heap scan)

SELECT COUNT(*) FROM ci_namespace_mirrors WHERE traversal_ids @> '{4249178}';
Query plan

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/53317/commands/155322

Time: 46.006 s
  - planning: 1.138 ms
  - execution: 46.005 s

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 84413 (~659.50 MiB) from the OS file cache, including disk I/O
  - dirtied: 1438 (~11.20 MiB)
  - writes: 0
Aggregate  (cost=243224.06..243224.07 rows=1 width=8) (actual time=46004.506..46004.519 rows=1 loops=1)
   Buffers: shared hit=1 read=84413 dirtied=1438
   WAL: records=2796 fpi=1438 bytes=11825252
   ->  Bitmap Heap Scan on public.ci_namespace_mirrors  (cost=1059.39..242955.67 rows=107357 width=0) (actual time=254.367..45970.637 rows=103854 loops=1)
         Buffers: shared hit=1 read=84413 dirtied=1438
         WAL: records=2796 fpi=1438 bytes=11825252
         ->  Bitmap Index Scan using index_gin_ci_namespace_mirrors_on_traversal_ids  (cost=0.00..1032.55 rows=107357 width=0) (actual time=236.900..236.907 rows=103866 loops=1)
               Index Cond: (ci_namespace_mirrors.traversal_ids @> '{4249178}'::integer[])
               Buffers: shared hit=1 read=292
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'

After — unnest covering index, root group (~1.6s, index-only scan)

SELECT COUNT(*) FROM ci_namespace_mirrors WHERE (traversal_ids[1]) IN (4249178);
Query plan

https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/53317/commands/155324

Time: 1.594 s
  - planning: 0.774 ms
  - execution: 1.593 s

Shared buffers:
  - hits: 26030 (~203.40 MiB) from the buffer pool
  - reads: 5719 (~44.70 MiB) from the OS file cache, including disk I/O
  - dirtied: 1430 (~11.20 MiB)
  - writes: 0
Aggregate  (cost=4255.56..4255.57 rows=1 width=8) (actual time=1592.772..1592.773 rows=1 loops=1)
   Buffers: shared hit=26030 read=5719 dirtied=1430
   WAL: records=2788 fpi=1430 bytes=11751376
   ->  Index Only Scan using index_ci_namespace_mirrors_on_traversal_ids_unnest on public.ci_namespace_mirrors  (cost=0.57..3987.17 rows=107357 width=0) (actual time=7.169..1585.644 rows=103854 loops=1)
         Index Cond: ((ci_namespace_mirrors.traversal_ids[1]) = 4249178)
         Heap Fetches: 4655
         Buffers: shared hit=26030 read=5719 dirtied=1430
         WAL: records=2788 fpi=1430 bytes=11751376
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'

After — non-root depth-3 subgroup, multi-column prefix (~1.5ms, index-only scan)

For a non-root group the descendant match uses the multi-column prefix form built by contains_traversal_ids, which also resolves via the unnest covering index.

SELECT COUNT(*) FROM ci_namespace_mirrors
  WHERE (traversal_ids[1], traversal_ids[2], traversal_ids[3]) IN ((4249178, 2459480, 6116193));
Query plan

https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/53317/commands/155327

Time: 1.511 ms
  - planning: 0.785 ms
  - execution: 0.726 ms

Shared buffers:
  - hits: 6 (~48.00 KiB) from the buffer pool
  - reads: 10 (~80.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
Aggregate  (cost=3.59..3.60 rows=1 width=8) (actual time=0.660..0.661 rows=1 loops=1)
   Buffers: shared hit=6 read=10
   ->  Index Only Scan using index_ci_namespace_mirrors_on_traversal_ids_unnest on public.ci_namespace_mirrors  (cost=0.57..3.59 rows=1 width=0) (actual time=0.533..0.652 rows=24 loops=1)
         Index Cond: (((ci_namespace_mirrors.traversal_ids[1]) = 4249178) AND ((ci_namespace_mirrors.traversal_ids[2]) = 2459480) AND ((ci_namespace_mirrors.traversal_ids[3]) = 6116193))
         Heap Fetches: 0
         Buffers: shared hit=6 read=10
Settings: work_mem = '100MB', effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4'

New code path (two queries)

The new Ci::NamespaceMirror.by_group_and_descendants_with_traversal_ids_index scope first looks up the group's own traversal_ids (a single-row read on the unique index_ci_namespace_mirrors_on_namespace_id), then builds the prefix IN (...) descendant match shown above.

SELECT traversal_ids FROM ci_namespace_mirrors WHERE namespace_id = 4249178 LIMIT 1;
Query plan

https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/53317/commands/155326

Time: 8.481 ms
  - planning: 0.837 ms
  - execution: 7.644 ms

Shared buffers:
  - hits: 0 from the buffer pool
  - reads: 5 (~40.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
Limit  (cost=0.57..3.58 rows=1 width=28) (actual time=7.598..7.600 rows=1 loops=1)
   Buffers: shared read=5
   ->  Index Scan using index_ci_namespace_mirrors_on_namespace_id on public.ci_namespace_mirrors  (cost=0.57..3.58 rows=1 width=28) (actual time=7.596..7.597 rows=1 loops=1)
         Index Cond: (ci_namespace_mirrors.namespace_id = 4249178)
         Buffers: shared read=5
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'

End-to-end count query

The full getGroupRunnersCountEE count over the group + project runner UNION ALL, for root namespace 4249178: ~46s → ~1.8s.

Before — GIN path (~46s)

SELECT COUNT(*) FROM (
  (SELECT "ci_runners".* FROM "ci_runners"
     INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
     WHERE "ci_runner_namespaces"."namespace_id" IN (
       SELECT "ci_namespace_mirrors"."namespace_id" FROM "ci_namespace_mirrors"
         WHERE (traversal_ids @> '{4249178}')))
  UNION ALL
  (SELECT DISTINCT "ci_runners".* FROM "ci_runners"
     INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
     WHERE "ci_runner_projects"."project_id" IN (
       SELECT "ci_project_mirrors"."project_id" FROM "ci_project_mirrors"
         WHERE "ci_project_mirrors"."namespace_id" IN (
           SELECT "ci_namespace_mirrors"."namespace_id" FROM "ci_namespace_mirrors"
             WHERE (traversal_ids @> '{4249178}'))))
) ci_runners;
Query plan

https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/53317/commands/155330

Time: 46.111 s
  - planning: 6.753 ms
  - execution: 46.104 s

Shared buffers:
  - hits: 1767789 (~13.50 GiB) from the buffer pool
  - reads: 357102 (~2.70 GiB) from the OS file cache, including disk I/O
  - dirtied: 5799 (~45.30 MiB)
  - writes: 1165 (~9.10 MiB)
Aggregate  (cost=630005.98..630005.99 rows=1 width=8) (actual time=46099.743..46101.433 rows=1 loops=1)
   Buffers: shared hit=1767789 read=357102 dirtied=5799 written=1165
   ->  Append  (cost=1001.15..629997.01 rows=3588 width=0) (actual time=3063.365..46101.267 rows=982 loops=1)
         ->  Subquery Scan on *SELECT* 1  (cost=1001.15..140351.84 rows=1376 width=0) (actual time=3063.364..25998.715 rows=473 loops=1)
               ->  Gather  (actual time=3063.363..25998.601 rows=473 loops=1)
                     Workers Planned: 2
                     ->  Nested Loop  (actual time=3058.642..25831.359 rows=158 loops=3)
                           ->  Nested Loop  (actual time=3056.305..25738.564 rows=158 loops=3)
                                 ->  Parallel Index Scan using index_ci_runner_namespaces_on_namespace_id (actual time=3.816..1982.800 rows=329186 loops=3)
                                 ->  Memoize  (actual time=0.072..0.072 rows=0 loops=987559)
                                       ->  Index Scan using index_ci_namespace_mirrors_on_namespace_id (actual time=0.635..0.635 rows=0 loops=111487)
                                             Index Cond: (namespace_id = ci_runner_namespaces.namespace_id)
                                             Filter: (traversal_ids @> '{4249178}'::integer[])
                                             Rows Removed by Filter: 1
         ->  Subquery Scan on *SELECT* 2  (cost=489461.34..489627.24 rows=2212 width=0) (actual time=20100.097..20102.450 rows=509 loops=1)
               ->  Unique  (actual time=20100.065..20102.377 rows=509 loops=1)
                     ->  Sort  (actual time=20100.052..20101.638 rows=1129 loops=1)
                           Sort Key: ci_runners_5.id, ci_runners_5.creator_id, ... (24 columns)
                           ->  Nested Loop  (actual time=16544.199..20097.596 rows=1129 loops=1)
                                 ->  HashAggregate  (actual time=16470.967..16501.476 rows=87605 loops=1)
                                       ->  Parallel Bitmap Heap Scan on ci_namespace_mirrors (actual time=50.988..885.491 rows=34618 loops=3)
                                             ->  Bitmap Index Scan using index_gin_ci_namespace_mirrors_on_traversal_ids (actual time=42.172..42.173 rows=103866 loops=1)
                                                   Index Cond: (traversal_ids @> '{4249178}'::integer[])
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'

After — unnest covering index path (~1.8s)

SELECT COUNT(*) FROM (
  (SELECT "ci_runners".* FROM "ci_runners"
     INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
     WHERE "ci_runner_namespaces"."namespace_id" IN (
       SELECT "ci_namespace_mirrors"."namespace_id" FROM "ci_namespace_mirrors"
         WHERE (traversal_ids[1]) IN (4249178)))
  UNION ALL
  (SELECT DISTINCT "ci_runners".* FROM "ci_runners"
     INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
     WHERE "ci_runner_projects"."project_id" IN (
       SELECT "ci_project_mirrors"."project_id" FROM "ci_project_mirrors"
         WHERE "ci_project_mirrors"."namespace_id" IN (
           SELECT "ci_namespace_mirrors"."namespace_id" FROM "ci_namespace_mirrors"
             WHERE (traversal_ids[1]) IN (4249178))))
) ci_runners;
Query plan

https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/53317/commands/155334

Time: 1.846 s
  - planning: 6.475 ms
  - execution: 1.839 s

Shared buffers:
  - hits: 1440430 (~11.00 GiB) from the buffer pool
  - reads: 106806 (~834.40 MiB) from the OS file cache, including disk I/O
  - dirtied: 4990 (~39.00 MiB)
  - writes: 0
Aggregate  (cost=285292.71..285292.72 rows=1 width=8) (actual time=1836.157..1837.283 rows=1 loops=1)
   Buffers: shared hit=1440430 read=106806 dirtied=4990
   ->  Append  (cost=4920.64..285283.74 rows=3588 width=0) (actual time=320.016..1837.205 rows=982 loops=1)
         ->  Subquery Scan on *SELECT* 1  (cost=4920.64..34450.50 rows=1376 width=0) (actual time=320.014..604.006 rows=473 loops=1)
               ->  Gather  (actual time=320.013..603.931 rows=473 loops=1)
                     Workers Planned: 2
                     ->  Nested Loop  (actual time=315.576..598.245 rows=158 loops=3)
                           ->  Parallel Hash Join  (actual time=314.203..581.297 rows=158 loops=3)
                                 Hash Cond: (ci_runner_namespaces.namespace_id = ci_namespace_mirrors.namespace_id)
                                 ->  Parallel Index Scan using index_ci_runner_namespaces_on_namespace_id (actual time=0.676..295.297 rows=329186 loops=3)
                                 ->  Parallel Hash  (actual time=245.771..245.772 rows=34618 loops=3)
                                       ->  Parallel Index Only Scan using index_ci_namespace_mirrors_on_traversal_ids_unnest (actual time=0.250..234.985 rows=34618 loops=3)
                                             Index Cond: ((traversal_ids[1]) = 4249178)
                                             Heap Fetches: 4655
         ->  Subquery Scan on *SELECT* 2  (cost=250649.40..250815.30 rows=2212 width=0) (actual time=1232.293..1233.125 rows=509 loops=1)
               ->  Unique  (actual time=1232.291..1233.088 rows=509 loops=1)
                     ->  Sort  (actual time=1232.286..1232.379 rows=1129 loops=1)
                           Sort Key: ci_runners_5.id, ci_runners_5.creator_id, ... (24 columns)
                           ->  Nested Loop  (actual time=931.098..1229.781 rows=1129 loops=1)
                                 ->  HashAggregate  (actual time=928.918..948.104 rows=87605 loops=1)
                                       ->  Nested Loop  (actual time=1.193..907.066 rows=29202 loops=3)
                                             ->  Parallel Index Only Scan using index_ci_namespace_mirrors_on_traversal_ids_unnest (actual time=0.041..14.616 rows=34618 loops=3)
                                                   Index Cond: ((traversal_ids[1]) = 4249178)
                                                   Heap Fetches: 4643
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'

How to set up and validate locally

  1. Create a group with descendant groups/projects and group/project runners.

  2. Notice the use of @> on the logs:

      Ci::Runner Count (4.4ms)  SELECT COUNT(*) FROM ((SELECT "ci_runners".* FROM "ci_runners" INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id" WHERE "ci_runner_namespaces"."namespace_id" IN (SELECT "ci_namespace_mirrors"."namespace_id" FROM "ci_namespace_mirrors" WHERE (traversal_ids @> '{24}')))
UNION ALL
(SELECT DISTINCT "ci_runners".* FROM "ci_runners" INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id" WHERE "ci_runner_projects"."project_id" IN (SELECT "ci_project_mirrors"."project_id" FROM "ci_project_mirrors" WHERE "ci_project_mirrors"."namespace_id" IN (SELECT "ci_namespace_mirrors"."namespace_id" FROM "ci_namespace_mirrors" WHERE (traversal_ids @> '{24}'))))) ci_runners WHERE "ci_runners"."contacted_at" > '2026-06-30 11:12:40.476118' /*application:web,correlation_id:01KWCAJ3NFPDNS00H0KQFNNYED,endpoint_id:GraphqlController#execute,db_config_database:gitlabhq_development_ci,db_config_name:ci,line:/app/graphql/types/countable_connection_type.rb:31:in `count'*/
  ↳ app/graphql/types/countable_connection_type.rb:31:in `count'

  3. Enable the feature flag:

    Feature.enable(:ci_runners_count_traversal_ids_index)

  4. Refresh the group Runners page (Group > Build > Runners) and confirm the count renders.

  5. Notice the use of IN on the logs:

      Ci::Runner Count (1.8ms)  SELECT COUNT(*) FROM ((SELECT "ci_runners".* FROM "ci_runners" INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id" WHERE "ci_runner_namespaces"."namespace_id" IN (SELECT "ci_namespace_mirrors"."namespace_id" FROM ((SELECT "ci_namespace_mirrors".* FROM "ci_namespace_mirrors" WHERE (((traversal_ids[1])) IN ((24))))) ci_namespace_mirrors))
UNION ALL
(SELECT DISTINCT "ci_runners".* FROM "ci_runners" INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id" WHERE "ci_runner_projects"."project_id" IN (SELECT "ci_project_mirrors"."project_id" FROM "ci_project_mirrors" WHERE "ci_project_mirrors"."namespace_id" IN (SELECT "ci_namespace_mirrors"."namespace_id" FROM ((SELECT "ci_namespace_mirrors".* FROM "ci_namespace_mirrors" WHERE (((traversal_ids[1])) IN ((24))))) ci_namespace_mirrors)))) ci_runners WHERE "ci_runners"."contacted_at" > '2026-06-30 11:13:42.564702' /*application:web,correlation_id:01KWCAM0BZTYQTHNJ1VJJYC47H,endpoint_id:GraphqlController#execute,db_config_database:gitlabhq_development_ci,db_config_name:ci,line:/app/graphql/types/countable_connection_type.rb:31:in `count'*/
  ↳ app/graphql/types/countable_connection_type.rb:31:in `count'

  6. Confirm the count matches with the flag disabled (Feature.disable(...)) — the result set is identical; only the query plan changes.

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.

Edited by Pedro Pombeiro (OOO from July 6th-17th)

Merge request reports