Runner owner lookup scans 897k rows on ci_runner_projects (queryid 3048765254806892336)

Summary

A ci_runner_projects lookup on the runner job-request path can scan most of the table to return one row. It runs on every job request from a project-type runner. On the worst measured parameter it reads 869,650 buffers where 4 would do. This affects PostgreSQL 17 and 18 equally — it is a missing index, not a version regression.

The query

Confirmed present in pg_stat_statements on the GPRD CI cluster with queryid 3048765254806892336:

SELECT "ci_runner_projects".* FROM "ci_runner_projects"
WHERE "ci_runner_projects"."runner_id" = $1
ORDER BY "ci_runner_projects"."id" ASC LIMIT $2

Production sample from patroni-ci-v18-105: 551,698 calls, 550,649 rows, 1,647,275 ms total — a 2.99 ms mean for a single-row lookup.

Where it comes from

  • has_one :owner_runner_project, -> { order(:id) } — app/models/ci/runner.rb:109
  • Ci::Runner#owner — app/models/ci/runner.rb:467
  • Gitlab::ApplicationContext#runner_project — lib/gitlab/application_context.rb:319-325
  • Gitlab::ApplicationContext.push(runner: current_runner) — lib/api/ci/helpers/runner.rb:213-215
  • before hook on the :jobs resource — lib/api/ci/runner.rb:196,200

The context is resolved for log enrichment (project_path, root_namespace_path), so the query runs on the /api/v4/jobs/* endpoints for every project-type runner.

Why it is slow

ci_runner_projects has a primary key on (id), an index on (project_id), and a unique index on (runner_id, project_id). Nothing serves WHERE runner_id = $1 ORDER BY id.

The planner therefore has two options: scan (runner_id, project_id) then sort, or walk the primary key in id order and filter on runner_id. For runners with many projects it estimates ~993 matching rows, expects a match after roughly 1,795 rows of primary-key walk, and prices that at 114 — cheaper than sorting 993 rows. The rows are in fact clustered near the top of the id range, so it scanned 897,780 rows before the first match. The failure is the planner's uniformity assumption.

Measured

runner_id = 34703594 (984 projects), table is 1,783,769 rows / 124 MB, on a PG18 clone:

Current With (runner_id, id)
Plan Index Scan on ci_runner_projects_pkey + Filter Index Scan on (runner_id, id)
Buffers 869,650 4
Rows Removed by Filter 897,780 0
Execution 500 ms 0.085 ms

Affected population

1,243,888 runners, mean 1.43 projects, max 984. 0.81% have more than 10 projects; 0.04% more than 100.

Fix

Add a (runner_id, id) index on ci_runner_projects. The index serves the predicate and the ordering together, so LIMIT 1 exits after one row and the primary-key walk is no longer cost-competitive.

Rejected alternative

Reordering on an already-indexed column (order(:project_id), served by the existing unique index) would need no migration, but changes which record wins for multi-project runners. owner feeds GraphQL ownerProject, the routable-token payload (app/models/ci/runner.rb:22-36), and audit paths, so that is not a safe substitute.

Acceptance criteria

  • (runner_id, id) index added to ci_runner_projects.
  • Plan verified on PG17 and PG18 clones, before and after.
  • Confirmed the same pathology does not apply to ci_runner_namespaces.
Edited by 🤖 GitLab Bot 🤖