Skip to content

Creates asynchronously index on ci_job_artifacts table

Max Orefice requested to merge morefice/prepare-async-index-job-artifact into master

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.

Edited by Max Orefice

Merge request reports