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.
Edited by Pedro Pombeiro