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)
Edited by Albert