Add index to optimize loading pipeline charts
The pipeline charts were hitting a database timeout because the SQL
query used ORDER BY id desc
, which caused the database to scan all
rows instead of only the rows indexed by project_id
. To fix this
query, we create an index on (project_id, id DESC).
Closes #118746 (closed)
This has been moved to a post-deploy migration because index creation on GitLab.com took about 24 minutes.
Index size: 2 GB (!)
Before
SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 9081391 ORDER BY "ci_pipelines"."id" DESC LIMIT 30;
Limit (cost=0.57..14915.09 rows=30 width=297) (actual time=1337026.356..1337026.398 rows=30 loops=1)
Buffers: shared hit=19463779 read=1615661 dirtied=27873 written=31286
I/O Timings: read=1296789.807 write=7496.768
-> Index Scan using ci_pipelines_pkey on public.ci_pipelines (cost=0.57..12437716.61 rows=25018 width=297) (actual time=1337026.353..1337026.390 rows=30 loops=1)
Filter: (ci_pipelines.project_id = 9081391)
Rows Removed by Filter: 21270513
Buffers: shared hit=19463779 read=1615661 dirtied=27873 written=31286
I/O Timings: read=1296789.807 write=7496.768
Time: 22.284 min
- planning: 3.582 ms
- execution: 22.284 min
- I/O read: 21.613 min
- I/O write: 7.497 s
Shared buffers:
- hits: 19463779 (~148.50 GiB) from the buffer pool
- reads: 1615661 (~12.30 GiB) from the OS file cache, including disk I/O
- dirtied: 27873 (~217.80 MiB)
After
create index concurrently index_ci_pipelines_on_project_id_and_id_desc ON ci_pipelines (project_id, id DESC)
Limit (cost=0.57..45.72 rows=30 width=297) (actual time=0.067..0.383 rows=30 loops=1)
Buffers: shared hit=32
-> Index Scan using index_ci_pipelines_on_project_id_and_id_desc on public.ci_pipelines (cost=0.57..37747.33 rows=25078 width=297) (actual time=0.067..0.381 rows=30 loops=1)
Index Cond: (ci_pipelines.project_id = 9081391)
Buffers: shared hit=32
Time: 2.934 ms
- planning: 2.466 ms
- execution: 0.468 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 32 (~256.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Edited by Stan Hu