Skip to content

Replace index_ci_runners_on_runner_type to include id

Pedro Pombeiro requested to merge pedropombeiro/377963/replace-ci_runners_index into master

What does this MR do and why?

Describe in detail what your merge request does and why.

This MR is a follow-up to !107694 (comment 1223110723) to include id in the index. This speeds up queries that select record ids based on runner_type, requiring an index-only scan instead of an index scan.

Part of #377963 (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

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

Database query execution plans

Query with existing index 
SELECT id
FROM ci_runners
WHERE runner_type = 1

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/14163/commands/49695

 Index Scan using index_ci_runners_on_runner_type on public.ci_runners  (cost=0.43..95.94 rows=120 width=4) (actual time=0.097..0.273 rows=73 loops=1)
   Index Cond: (ci_runners.runner_type = 1)
   Buffers: shared hit=78
   I/O Timings: read=0.000 write=0.000
Query with new index 
SELECT id
FROM ci_runners
WHERE runner_type = 1

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/14163/commands/49698

 Index Only Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners  (cost=0.43..10.04 rows=97 width=4) (actual time=0.148..0.224 rows=73 loops=1)  
   Index Cond: (ci_runners.runner_type = 1)  
   Heap Fetches: 12  
   Buffers: shared hit=38 read=3  
   I/O Timings: read=0.055 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.

Edited by Pedro Pombeiro

Merge request reports