Filter runners by tag IDs in one tagging subquery

What does this MR do and why?

Context

The REST runner lists, such as GET /groups/:id/runners?tag_list=..., filter by exact tag names with one subquery per tag that finds the tag by name. Postgres can't estimate from a name how many runners carry the tag, so on groups with many runners it picks a slow plan, and every extra tag adds its own subquery to plan. The figures behind this are in https://gitlab.com/gitlab-org/gitlab/-/work_items/638241#note_4175532904.

This MR

  • Looks up the tag IDs first, then matches all the tags with one subquery on the runner taggings, correlated on the runner type so Postgres can prune partitions. When a tag doesn't exist, it returns no runners without running the runner query. The runners returned don't change.
  • Covers every exact tag filter: the REST runner lists and the GitLab Duo runner checks. Substring matching, which the GraphQL runner lists use, is unchanged.
  • The admin GET /runners/all count with several tags gets cheaper, but it still reads the taggings of every runner with those tags.

This keeps the tag filter's cost on the runners in scope, whatever the number of tags.

Feature Flag: ci_runner_tag_filter_by_tag_ids

References

Database queries

See MR comments.

How to set up and validate locally

  1. Run this with a personal access token (read_api scope) of a group Owner, for a group with runners tagged docker and linux:
    curl --head --header "PRIVATE-TOKEN: <your_access_token>" "http://gdk.test:3000/api/v4/groups/<id>/runners?tag_list=docker,linux"
    Note the X-Total header.
  2. In a Rails console, run Feature.enable(:ci_runner_tag_filter_by_tag_ids) and wait a minute.
  3. Run the curl again. X-Total is the same, and log/development.log shows SELECT "tags"."id" FROM "tags" WHERE "tags"."name" IN ('docker', 'linux') LIMIT 2, then the count with "ci_runner_taggings"."tag_id" IN (<id>, <id>) and = 2.

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 Leaminn Ma

Merge request reports

Loading
Loading