Creates asynchronously index on ci_job_artifacts table
Ref: #362640 (closed)
What does this MR do and why?
This MR prepares an async index on ci_job_artifacts: (id, created_at, project_id)
.
This index will be used to order job artifacts by id
instead of created_at
.
To prevent a typebug from happening as pointed out by @fabiopitino
when an object gets saved and created_at
is filled in before the id
.
This will be used to recalculate job artifacts size which is currently broken for some projects.
Database queries
Migration:
CREATE INDEX index_ci_job_artifacts_on_id_and_created_at_and_project_id ON ci_job_artifacts USING btree (id, created_at, project_id);
SELECT query without index
SELECT ci_job_artifacts.id, ci_job_artifacts.size
FROM ci_job_artifacts
WHERE ci_job_artifacts.project_id = 278964
AND ci_job_artifacts.created_at <= '2022-05-06 13:30:22.577945'
AND ci_job_artifacts.id > 13
ORDER BY ci_job_artifacts.id ASC
LIMIT 1000;
SELECT query with index
SELECT ci_job_artifacts.id, ci_job_artifacts.size
FROM ci_job_artifacts
WHERE ci_job_artifacts.project_id = 278964
AND ci_job_artifacts.created_at <= '2022-05-06 13:30:22.577945'
AND ci_job_artifacts.id > 13
ORDER BY ci_job_artifacts.id ASC
LIMIT 1000;
Query plan
Used a dedicated thin clone created specially for this otherwise the data will expire after 24 hours
.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.58..1104.29 rows=1000 width=16) (actual time=2.663..796.437 rows=1000 loops=1)
-> Index Scan using index_ci_job_artifacts_on_id_and_created_at_and_project_id on ci_job_artifacts (cost=0.58..80447760.58 rows=72888654 width=16) (actual time=2.661..796.055 rows=1000 loops=1)
Index Cond: ((id > 13) AND (created_at <= '2022-05-06 13:30:22.577945+00'::timestamp with time zone) AND (project_id = 278964))
Planning Time: 0.298 ms
Execution Time: 796.735 ms
(5 rows)
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 Max Orefice