Prepare async index on ci_job_artifacts id, project_id, file_type

What does this MR do and why?

Prepares async index on ci_job_artifacts: (id, project_id, file_type).

This index will be used in the feature to delete project job artifacts in bulk.

Database queries

Migration:

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

SELECT query:

SELECT "id"
FROM (WITH RECURSIVE "array_cte"
              AS MATERIALIZED (SELECT "file_type" FROM (VALUES (1), (2), (4), (5), (6), (7), (8), (9), (101), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27)) AS file_types (file_type)),
     "recursive_keyset_cte" AS ((SELECT NULL::bigint AS id, array_cte_file_type_array, ci_job_artifacts_id_array, 0::bigint AS count FROM (SELECT ARRAY_AGG("array_cte"."file_type") AS array_cte_file_type_array, ARRAY_AGG("ci_job_artifacts"."id") AS ci_job_artifacts_id_array FROM (SELECT "array_cte"."file_type" FROM array_cte) array_cte LEFT JOIN LATERAL (SELECT "ci_job_artifacts"."id" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."project_id" = 26 AND "ci_job_artifacts"."file_type" = "array_cte"."file_type" ORDER BY "ci_job_artifacts"."id" ASC LIMIT 1) ci_job_artifacts ON TRUE WHERE "ci_job_artifacts"."id" IS NOT NULL) array_scope_lateral_query LIMIT 1)
UNION ALL
(
SELECT recursive_keyset_cte.ci_job_artifacts_id_array[position], array_cte_file_type_array, recursive_keyset_cte.ci_job_artifacts_id_array[:position_query.position-1]||next_cursor_values.id||recursive_keyset_cte.ci_job_artifacts_id_array[position_query.position+1:], recursive_keyset_cte.count + 1
FROM recursive_keyset_cte,
    LATERAL (SELECT id, position FROM UNNEST(ci_job_artifacts_id_array) WITH ORDINALITY AS u(id, position) WHERE id IS NOT NULL ORDER BY 1 ASC LIMIT 1) AS position_query,
    LATERAL (SELECT "record"."id" FROM (VALUES (NULL)) AS nulls LEFT JOIN (SELECT "ci_job_artifacts"."id" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."project_id" = 278964 AND "ci_job_artifacts"."file_type" = recursive_keyset_cte.array_cte_file_type_array[position] AND ("ci_job_artifacts"."id" > recursive_keyset_cte.ci_job_artifacts_id_array[position]) ORDER BY "ci_job_artifacts"."id" ASC LIMIT 1) record ON TRUE LIMIT 1) AS next_cursor_values
    ))
SELECT id
FROM "recursive_keyset_cte" AS "ci_job_artifacts"
WHERE (count <> 0)) ci_job_artifacts LIMIT 1000

Query plan: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7797/commands/27802

Update query:

UPDATE "ci_job_artifacts"
SET "locked"    = 0,
    "expire_at" = '2021 - 12 - 28 10:48:09.406304'
WHERE "ci_job_artifacts"."id" IN (SELECT "ci_job_artifacts"."id" FROM "ci_job_artifacts" INNER JOIN "ci_builds" ON "ci_builds"."id" = "ci_job_artifacts"."job_id"
  AND "ci_builds"."type" = 'Ci::Build' INNER JOIN "ci_pipelines" ON "ci_pipelines"."id" = "ci_builds"."commit_id" WHERE "ci_pipelines"."locked" = 0 AND "ci_job_artifacts"."id" IN (13263, 13264, 13265, 13266, 13267, 13268, 13269, 13270, 13271, 13272, 13368, 13369, 13370, 13371, 13372, 13373, 13374, 13375, 13376, 13377))

Query plan: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7797/commands/27803

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.

Related to #223793 (closed)