Creates asynchronously index on ci_job_artifacts table
Ref: #238536 (closed)
What does this MR do and why?
Prepares async index on ci_job_artifacts: (id, created_at, file_type).
This index will be used in the feature to recalculate job artifacts size which is currently broken.
Database queries
Migration:
CREATE INDEX index_ci_job_artifacts_on_id_project_id_and_created_at ON ci_job_artifacts USING btree (project_id, created_at, id);
SELECT query:
SELECT "ci_job_artifacts"."id", "ci_job_artifacts"."size" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."project_id" = 278964 AND (created_at <= '2022-03-10 12:58:18.601374' AND id > 165) ORDER BY "ci_job_artifacts"."created_at" ASC LIMIT 1000;
Query plan
Used with thin clone created specially for this otherwise the data will expire after 24 hours
.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.70..628.91 rows=1000 width=24) (actual time=13.053..507.649 rows=1000 loops=1)
-> Index Scan using index_ci_job_artifacts_on_id_project_id_and_created_at on ci_job_artifacts (cost=0.70..54239796.66 rows=86340333 width=24) (actual time=13.050..507.274 rows=1000 loops=1)
Index Cond: ((project_id = 278964) AND (created_at <= '2022-03-10 12:58:18.601374+00'::timestamp with time zone) AND (id > 165))
Planning Time: 0.345 ms
Execution Time: 508.073 ms
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