Admin runner filtering by version

What does this MR do and why?

This MR lets admins search by runner version on the admin runners page. When searching by a specific version, all runners that do not start with the searched runner version will be filtered out of the runner list. For example, searching for 14. could result 14.11.1 or 14.2.3 but not 143.2.1. If searching for 14 in the previous example, we would also get 143.2.1.

This MR does not implement search suggestions and instead defaults to two set suggestions of 15.11.0 and 15.12.0. Search suggestions would be good to add onto this MR but it might make it too big.

Part of #422046 (closed)

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

Make sure you have at least one GitLab runner registered and running.

1. Go to http://gdk.test:3000/-/graphql-explorer

2. Use the following query:

   {
     runners(versionPrefix: "16") {
       nodes {
         id
         managers {
           nodes {
             systemId
             version
           }
         }
       }
     }
   }

3. The query will return any runners that have runner managers of version 16 attached.

Database query plans

Setup:

exec CREATE INDEX index_ci_runner_machines_on_major_version_trigram ON ci_runner_machines USING btree ("substring"(version, '\d+\.'::text), version, runner_id);

exec CREATE INDEX index_ci_runner_machines_on_minor_version_trigram ON ci_runner_machines USING btree ("substring"(version, '\d+\.\d+\.'::text), version, runner_id);

exec CREATE INDEX index_ci_runner_machines_on_patch_version_trigram ON ci_runner_machines USING btree ("substring"(version, '\d+\.\d+\.\d+'::text), version, runner_id);

Simple version query

Retrieve uncapped project runner count

This branch, with version filter

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/23071/commands/74264

SELECT COUNT(*)
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
  INNER JOIN "ci_runner_versions" runner_version ON runner_version.version = "ci_runner_machines".version
WHERE "ci_runners"."runner_type" = 3
  AND substring("ci_runner_machines"."version", '\d+\.\d+\.'::text) = '15.11.'

 Aggregate  (cost=2627.10..2627.11 rows=1 width=8) (actual time=50.992..50.996 rows=1 loops=1)
   Buffers: shared hit=43916
   I/O Timings: read=0.000 write=0.000
   ->  Merge Join  (cost=12.59..2624.87 rows=891 width=0) (actual time=0.622..50.140 rows=6176 loops=1)
         Merge Cond: (ci_runner_machines.version = runner_version.version)
         Buffers: shared hit=43916
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=0.85..2927.17 rows=1056 width=7) (actual time=0.098..48.141 rows=6176 loops=1)
               Buffers: shared hit=43861
               I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using index_ci_runner_machines_on_minor_version_trigram on public.ci_runner_machines  (cost=0.42..105.37 rows=1454 width=15) (actual time=0.015..5.987 rows=10819 loops=1)
                     Index Cond: (("substring"(ci_runner_machines.version, '\d+\.\d+\.'::text)) = '15.11.'::text)
                     Heap Fetches: 1815
                     Buffers: shared hit=5691
                     I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners  (cost=0.43..1.94 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=10819)
                     Index Cond: ((ci_runners.runner_type = 3) AND (ci_runners.id = ci_runner_machines.runner_id))
                     Heap Fetches: 2192
                     Buffers: shared hit=38170
                     I/O Timings: read=0.000 write=0.000
         ->  Index Only Scan using ci_runner_versions_pkey on public.ci_runner_versions runner_version  (cost=0.28..136.38 rows=2970 width=21) (actual time=0.016..0.278 rows=1213 loops=1)
               Heap Fetches: 62
               Buffers: shared hit=55
               I/O Timings: read=0.000 write=0.000

Retrieve page of 21 runners

This branch, with version filter

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/23071/commands/74265

SELECT "ci_runners".*
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
WHERE substring("ci_runner_machines"."version", '\d+\.\d+\.'::text) = '15.11.'
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 21

 Limit  (cost=5130.23..5130.29 rows=21 width=273) (actual time=30312.677..30312.684 rows=21 loops=1)
   Buffers: shared hit=35675 read=13301 dirtied=2479
   I/O Timings: read=29349.677 write=0.000
   ->  Sort  (cost=5130.23..5133.87 rows=1454 width=273) (actual time=30312.674..30312.678 rows=21 loops=1)
         Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
         Sort Method: top-N heapsort  Memory: 45kB
         Buffers: shared hit=35675 read=13301 dirtied=2479
         I/O Timings: read=29349.677 write=0.000
         ->  Nested Loop  (cost=0.85..5091.03 rows=1454 width=273) (actual time=3.377..30209.857 rows=10819 loops=1)
               Buffers: shared hit=35669 read=13301 dirtied=2479
               I/O Timings: read=29349.677 write=0.000
               ->  Index Only Scan using index_ci_runner_machines_on_minor_version_trigram on public.ci_runner_machines  (cost=0.42..105.37 rows=1454 width=8) (actual time=0.026..79.577 rows=10819 loops=1)
                     Index Cond: (("substring"(ci_runner_machines.version, '\d+\.\d+\.'::text)) = '15.11.'::text)
                     Heap Fetches: 1815
                     Buffers: shared hit=5691
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_runners_pkey on public.ci_runners  (cost=0.43..3.43 rows=1 width=273) (actual time=2.777..2.777 rows=1 loops=10819)
                     Index Cond: (ci_runners.id = ci_runner_machines.runner_id)
                     Buffers: shared hit=29978 read=13301 dirtied=2479
                     I/O Timings: read=29349.677 write=0.000

Complex query with various filters

Retrieve uncapped project runner count

master branch without version filter

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/21364/commands/69698

SELECT COUNT(*)
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
  INNER JOIN "ci_runner_versions" runner_version ON runner_version.version = "ci_runner_machines".version
WHERE "ci_runners"."active" = TRUE
  AND "ci_runners"."created_at" <= '2023-05-15 09:32:40.914414'
  AND ("ci_runners"."contacted_at" IS NULL
    OR "ci_runners"."contacted_at" <= '2023-05-15 09:32:40.914414')
  AND "runner_version"."status" = 3
  AND "ci_runners"."runner_type" = 3

 Aggregate  (cost=291021.20..291021.21 rows=1 width=8) (actual time=2060.723..2114.579 rows=1 loops=1)
   Buffers: shared hit=923946 read=104418 dirtied=64463
   I/O Timings: read=3774.733 write=0.000
   ->  Gather  (cost=291020.98..291021.19 rows=2 width=8) (actual time=2059.662..2114.566 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=923946 read=104418 dirtied=64463
         I/O Timings: read=3774.733 write=0.000
         ->  Aggregate  (cost=290020.98..290020.99 rows=1 width=8) (actual time=2053.416..2053.420 rows=1 loops=3)
               Buffers: shared hit=923946 read=104418 dirtied=64463
               I/O Timings: read=3774.733 write=0.000
               ->  Nested Loop  (cost=13.27..289855.82 rows=66065 width=0) (actual time=2053.409..2053.413 rows=0 loops=3)
                     Buffers: shared hit=923946 read=104418 dirtied=64463
                     I/O Timings: read=3774.733 write=0.000
                     ->  Merge Join  (cost=12.84..102117.88 rows=162985 width=8) (actual time=0.372..623.712 rows=77089 loops=3)
                           Merge Cond: (ci_runner_machines.version = runner_version.version)
                           Buffers: shared hit=157460 read=32387 dirtied=23430
                           I/O Timings: read=1104.953 write=0.000
                           ->  Parallel Index Scan using index_ci_runner_machines_on_version on public.ci_runner_machines  (cost=0.42..104529.27 rows=173062 width=15) (actual time=0.216..599.467 rows=79760 loops=3)
                                 Buffers: shared hit=157261 read=32329 dirtied=23423
                                 I/O Timings: read=1102.269 write=0.000
                           ->  Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version  (cost=0.28..140.87 rows=2453 width=21) (actual time=0.104..2.229 rows=1584 loops=3)
                                 Index Cond: (runner_version.status = 3)
                                 Heap Fetches: 623
                                 Buffers: shared hit=199 read=58 dirtied=7
                                 I/O Timings: read=2.683 write=0.000
                     ->  Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners  (cost=0.43..1.15 rows=1 width=4) (actual time=0.018..0.018 rows=0 loops=231267)
                           Index Cond: ((ci_runners.runner_type = 3) AND (ci_runners.id = ci_runner_machines.runner_id))
                           Filter: (ci_runners.active AND (ci_runners.created_at <= '2023-05-15 09:32:40.914414'::timestamp without time zone) AND ((ci_runners.contacted_at IS NULL) OR (ci_runners.contacted_at <= '2023-05-15 09:32:40.914414'::timestamp without time zone)))
                           Rows Removed by Filter: 1
                           Buffers: shared hit=766486 read=72031 dirtied=41033
                           I/O Timings: read=2669.781 write=0.000

This branch, with version filter

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/23071/commands/74266

SELECT COUNT(*)
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
  INNER JOIN "ci_runner_versions" runner_version ON runner_version.version = "ci_runner_machines".version
WHERE "ci_runners"."active" = TRUE
  AND "ci_runners"."created_at" <= '2023-05-15 09:40:23.437431'
  AND ("ci_runners"."contacted_at" IS NULL
    OR "ci_runners"."contacted_at" <= '2023-05-15 09:40:23.437431')
  AND "runner_version"."status" = 3
  AND "ci_runners"."runner_type" = 3
  AND substring("ci_runner_machines"."version", '\d+\.\d+\.\d+'::text) = '15.11.1'

 Aggregate  (cost=4312.40..4312.41 rows=1 width=8) (actual time=26.545..26.548 rows=1 loops=1)
   Buffers: shared hit=1664 read=15 dirtied=3
   I/O Timings: read=21.835 write=0.000
   ->  Nested Loop  (cost=8.88..4311.29 rows=445 width=0) (actual time=26.539..26.541 rows=0 loops=1)
         Buffers: shared hit=1664 read=15 dirtied=3
         I/O Timings: read=21.835 write=0.000
         ->  Merge Join  (cost=8.45..201.48 rows=1201 width=8) (actual time=22.591..23.333 rows=432 loops=1)
               Merge Cond: (ci_runner_machines.version = runner_version.version)
               Buffers: shared hit=229 read=15
               I/O Timings: read=21.835 write=0.000
               ->  Index Only Scan using index_ci_runner_machines_on_patch_version_trigram on public.ci_runner_machines  (cost=0.42..105.37 rows=1454 width=15) (actual time=9.362..10.008 rows=432 loops=1)
                     Index Cond: (("substring"(ci_runner_machines.version, '\d+\.\d+\.\d+'::text)) = '15.11.1'::text)
                     Heap Fetches: 90
                     Buffers: shared hit=202 read=5
                     I/O Timings: read=9.403 write=0.000
               ->  Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version  (cost=0.28..90.22 rows=2910 width=21) (actual time=3.882..12.932 rows=1208 loops=1)
                     Index Cond: (runner_version.status = 3)
                     Heap Fetches: 63
                     Buffers: shared hit=27 read=10
                     I/O Timings: read=12.432 write=0.000
         ->  Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners  (cost=0.43..3.42 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=432)
               Index Cond: ((ci_runners.runner_type = 3) AND (ci_runners.id = ci_runner_machines.runner_id))
               Filter: (ci_runners.active AND (ci_runners.created_at <= '2023-05-15 09:40:23.437431'::timestamp without time zone) AND ((ci_runners.contacted_at IS NULL) OR (ci_runners.contacted_at <= '2023-05-15 09:40:23.437431'::timestamp without time zone)))
               Rows Removed by Filter: 0
               Buffers: shared hit=1435 dirtied=3
               I/O Timings: read=0.000 write=0.000

Retrieve page of 21 runners

master branch without version filter

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/21364/commands/69705

SELECT "ci_runners".*
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
  INNER JOIN "ci_runner_versions" runner_version ON runner_version.version = "ci_runner_machines".version
WHERE "ci_runners"."active" = TRUE
  AND "ci_runners"."created_at" <= '2023-05-15 10:02:55.055500'
  AND ("ci_runners"."contacted_at" IS NULL
    OR "ci_runners"."contacted_at" <= '2023-05-15 10:02:55.055500')
  AND "runner_version"."status" = 3
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 21

 Limit  (cost=1.13..111.91 rows=21 width=276) (actual time=25806.407..25806.418 rows=0 loops=1)
   Buffers: shared hit=6084364 read=208308 dirtied=55673 written=20494
   I/O Timings: read=18392.120 write=609.862
   ->  Nested Loop  (cost=1.13..1146393.47 rows=217328 width=276) (actual time=25806.404..25806.415 rows=0 loops=1)
         Buffers: shared hit=6084364 read=208308 dirtied=55673 written=20494
         I/O Timings: read=18392.120 write=609.862
         ->  Nested Loop  (cost=0.85..1077114.10 rows=230765 width=283) (actual time=25806.394..25806.395 rows=0 loops=1)
               Buffers: shared hit=6084364 read=208308 dirtied=55673 written=20494
               I/O Timings: read=18392.120 write=609.862
               ->  Index Scan using index_ci_runners_on_created_at_desc_and_id_desc on public.ci_runners  (cost=0.43..308312.35 rows=1440305 width=276) (actual time=21.397..22191.689 rows=1686430 loops=1)
                     Index Cond: (ci_runners.created_at <= '2023-05-15 10:02:55.0555'::timestamp without time zone)
                     Filter: (ci_runners.active AND ((ci_runners.contacted_at IS NULL) OR (ci_runners.contacted_at <= '2023-05-15 10:02:55.0555'::timestamp without time zone)))
                     Rows Removed by Filter: 240876
                     Buffers: shared hit=1045167 read=188210 dirtied=55673 written=19025
                     I/O Timings: read=17658.094 write=564.974
               ->  Index Scan using index_ci_runner_machines_on_runner_id_and_system_xid on public.ci_runner_machines  (cost=0.42..0.52 rows=1 width=15) (actual time=0.002..0.002 rows=0 loops=1686430)
                     Index Cond: (ci_runner_machines.runner_id = ci_runners.id)
                     Buffers: shared hit=5039197 read=20098 written=1469
                     I/O Timings: read=734.026 write=44.888
         ->  Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version  (cost=0.28..0.30 rows=1 width=21) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: ((runner_version.status = 3) AND (runner_version.version = ci_runner_machines.version))
               Heap Fetches: 0
               I/O Timings: read=0.000 write=0.000

This branch, with version filter

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/23071/commands/74269

SELECT "ci_runners".*
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
  INNER JOIN "ci_runner_versions" runner_version ON runner_version.version = "ci_runner_machines".version
WHERE "ci_runners"."active" = TRUE
  AND "ci_runners"."created_at" <= '2023-05-15 10:06:01.772944'
  AND ("ci_runners"."contacted_at" IS NULL
    OR "ci_runners"."contacted_at" <= '2023-05-15 10:06:01.772944')
  AND "runner_version"."status" = 3
  AND substring("ci_runner_machines"."version", '\d+\.\d+\.'::text) = '15.11.'
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 21

 Limit  (cost=4341.44..4341.50 rows=21 width=273) (actual time=10637.304..10637.308 rows=0 loops=1)
   Buffers: shared hit=42849 read=6218 dirtied=767
   I/O Timings: read=10304.432 write=0.000
   ->  Sort  (cost=4341.44..4342.98 rows=613 width=273) (actual time=10637.301..10637.304 rows=0 loops=1)
         Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=42849 read=6218 dirtied=767
         I/O Timings: read=10304.432 write=0.000
         ->  Nested Loop  (cost=8.88..4324.92 rows=613 width=273) (actual time=10637.251..10637.254 rows=0 loops=1)
               Buffers: shared hit=42843 read=6218 dirtied=767
               I/O Timings: read=10304.432 write=0.000
               ->  Merge Join  (cost=8.45..201.48 rows=1201 width=8) (actual time=0.523..58.476 rows=10819 loops=1)
                     Merge Cond: (ci_runner_machines.version = runner_version.version)
                     Buffers: shared hit=5724
                     I/O Timings: read=0.000 write=0.000
                     ->  Index Only Scan using index_ci_runner_machines_on_minor_version_trigram on public.ci_runner_machines  (cost=0.42..105.37 rows=1454 width=15) (actual time=0.018..43.688 rows=10819 loops=1)
                           Index Cond: (("substring"(ci_runner_machines.version, '\d+\.\d+\.'::text)) = '15.11.'::text)
                           Heap Fetches: 1815
                           Buffers: shared hit=5691
                           I/O Timings: read=0.000 write=0.000
                     ->  Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version  (cost=0.28..90.22 rows=2910 width=21) (actual time=0.053..0.309 rows=1208 loops=1)
                           Index Cond: (runner_version.status = 3)
                           Heap Fetches: 60
                           Buffers: shared hit=33
                           I/O Timings: read=0.000 write=0.000
               ->  Index Scan using index_ci_runners_on_active on public.ci_runners  (cost=0.43..3.43 rows=1 width=273) (actual time=0.976..0.976 rows=0 loops=10819)
                     Index Cond: ((ci_runners.active = true) AND (ci_runners.id = ci_runner_machines.runner_id))
                     Filter: ((ci_runners.created_at <= '2023-05-15 10:06:01.772944'::timestamp without time zone) AND ((ci_runners.contacted_at IS NULL) OR (ci_runners.contacted_at <= '2023-05-15 10:06:01.772944'::timestamp without time zone)))
                     Rows Removed by Filter: 1
                     Buffers: shared hit=37119 read=6218 dirtied=767
                     I/O Timings: read=10304.432 write=0.000

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

app/finders/ci/runners_finder.rb

@@ -20,6 +20,7 @@ def execute
       filter_by_upgrade_status!
       filter_by_runner_type!
       filter_by_tag_list!
+      filter_by_version_prefix!
       sort!
       request_tag_list!
 
@@ -113,6 +114,16 @@ def filter_by_tag_list!
       end
     end
 
+    def filter_by_version_prefix!
+      return @runners unless @params[:version_prefix]
+
+      sanitized_prefix = @params[:version_prefix][/^[\d+.]+/]
+
+      return @runners unless sanitized_prefix
+
+      @runners.with_version_prefix(sanitized_prefix)
+    end
+
     def sort!
       @runners = @runners.order_by(sort_key)
     end