Prepare async index to for Ci::JobArtifact

Ref: #362640 (closed)

What does this MR do and why?

This MR prepares an async index on ci_job_artifacts: (project_id, id).

Why are we doing this?

This index will be used to order job artifacts by id instead of created_at to recalculate job artifacts size which is currently broken for some projects.

Database queries

Migration:

CREATE INDEX index_ci_job_artifacts_on_project_id_and_id ON ci_job_artifacts USING btree (project_id, id);

NEW query without index

• 6.297 s

SELECT "ci_job_artifacts"."id", "ci_job_artifacts"."size"
FROM "ci_job_artifacts"
WHERE "ci_job_artifacts"."project_id" = 278964
AND "ci_job_artifacts"."id" > 500000
AND "ci_job_artifacts"."id" <= 700000
ORDER BY "ci_job_artifacts"."id" ASC
LIMIT 1000;

NEW query with index

• 10.255 ms

SELECT "ci_job_artifacts"."id", "ci_job_artifacts"."size"
FROM "ci_job_artifacts"
WHERE "ci_job_artifacts"."project_id" = 278964
AND "ci_job_artifacts"."id" > 500000
AND "ci_job_artifacts"."id" <= 700000
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..1434.18 rows=1000 width=24) (actual time=7.058..10.170 rows=686 loops=1)
   ->  Index Scan using index_ci_job_artifacts_on_project_id_and_id on ci_job_artifacts  (cost=0.58..4732.91 rows=3301 width=24) (actual time=7.055..10.070 rows=686 loops=1)
         Index Cond: ((project_id = 278964) AND (id > 500000) AND (id <= 700000))
 Planning Time: 9.688 ms
 Execution Time: 10.255 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.