Create trigram index for version on ci_runner_managers

What does this MR do and why?

Creates database trigram index for the version column of the ci_runner_machines table. This index allows an index to be used referencing versions with ILIKE.

This preliminary MR was requested for !118829 (merged) with this note.

The plan is to remove the existing btree index in the following milestone.

Part of #422046 (closed)

How to set up and validate locally

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

Database query plans

SELECT COUNT(*)
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_machines"."version" ILIKE '15.11%'

With existing index

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/21368/commands/69857

 Aggregate  (cost=157437.40..157437.41 rows=1 width=8) (actual time=64052.277..64103.232 rows=1 loops=1)
   Buffers: shared hit=264218 read=60768 dirtied=24274
   I/O Timings: read=108400.369 write=0.000
   ->  Gather  (cost=157437.19..157437.40 rows=2 width=8) (actual time=64047.447..64103.201 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=264218 read=60768 dirtied=24274
         I/O Timings: read=108400.369 write=0.000
         ->  Aggregate  (cost=156437.19..156437.20 rows=1 width=8) (actual time=64020.158..64020.162 rows=1 loops=3)
               Buffers: shared hit=264218 read=60768 dirtied=24274
               I/O Timings: read=108400.369 write=0.000
               ->  Nested Loop  (cost=0.85..156415.55 rows=8655 width=0) (actual time=359.633..64009.050 rows=4173 loops=3)
                     Buffers: shared hit=264218 read=60768 dirtied=24274
                     I/O Timings: read=108400.369 write=0.000
                     ->  Parallel Index Scan using index_ci_runner_machines_on_runner_id_and_system_xid on public.ci_runner_machines  (cost=0.42..140935.50 rows=8655 width=8) (actual time=359.028..63178.563 rows=4173 loops=3)
                           Filter: (ci_runner_machines.version ~~* '15.11%'::text)
                           Rows Removed by Filter: 79264
                           Buffers: shared hit=228227 read=52739 dirtied=23782
                           I/O Timings: read=106528.437 write=0.000
                     ->  Index Only Scan using ci_runners_pkey on public.ci_runners  (cost=0.43..1.79 rows=1 width=4) (actual time=0.188..0.188 rows=1 loops=12518)
                           Index Cond: (ci_runners.id = ci_runner_machines.runner_id)
                           Heap Fetches: 1687
                           Buffers: shared hit=35991 read=8029 dirtied=492
                           I/O Timings: read=1871.932 write=0.000

With trigram index

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/21368/commands/69855

 Aggregate  (cost=40609.99..40610.00 rows=1 width=8) (actual time=6896.502..6902.912 rows=1 loops=1)
   Buffers: shared hit=44692 read=6700 dirtied=1
   I/O Timings: read=18970.014 write=0.000
   ->  Gather  (cost=40609.77..40609.98 rows=2 width=8) (actual time=6896.402..6902.898 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=44692 read=6700 dirtied=1
         I/O Timings: read=18970.014 write=0.000
         ->  Aggregate  (cost=39609.77..39609.78 rows=1 width=8) (actual time=6871.127..6871.130 rows=1 loops=3)
               Buffers: shared hit=44692 read=6700 dirtied=1
               I/O Timings: read=18970.014 write=0.000
               ->  Nested Loop  (cost=116.95..39596.73 rows=5216 width=0) (actual time=19.839..6864.233 rows=4173 loops=3)
                     Buffers: shared hit=44692 read=6700 dirtied=1
                     I/O Timings: read=18970.014 write=0.000
                     ->  Parallel Bitmap Heap Scan on public.ci_runner_machines  (cost=116.52..29934.18 rows=5216 width=8) (actual time=6.812..200.997 rows=4173 loops=3)
                           Buffers: shared hit=7701
                           I/O Timings: read=0.000 write=0.000
                           ->  Bitmap Index Scan using index_ci_runner_machines_on_version_trigram  (cost=0.00..113.39 rows=12519 width=0) (actual time=17.251..17.252 rows=12525 loops=1)
                                 Index Cond: (ci_runner_machines.version ~~* '15.11%'::text)
                                 Buffers: shared hit=145
                                 I/O Timings: read=0.000 write=0.000
                     ->  Index Only Scan using ci_runners_pkey on public.ci_runners  (cost=0.43..1.85 rows=1 width=4) (actual time=1.587..1.587 rows=1 loops=12518)
                           Index Cond: (ci_runners.id = ci_runner_machines.runner_id)
                           Heap Fetches: 1687
                           Buffers: shared hit=36991 read=6700 dirtied=1
                           I/O Timings: read=18970.014 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.