index_ci_job_artifacts_on_expire_at_and_job_id is bloated and unused
Problem
While explaining my performance analysis of a prior merge request !76509 (comment 901250356), I wrote this as an explanation of why we're able to beat up this particular index in this particular way:
- The
index_ci_job_artifacts_on_expire_at_and_job_id
index is not a partial index, so it's got every row in the table. Maybe it should be a partial index, but that's another problem for another day.- There are ~325m rows with expiration dates, out of 3.7b rows in the table. Using
WHERE expire_at < #{Time.current}
andORDER BY expired_at ASC
, we avoid the large block of tuples with noexpire_at
value at one end of the index.- Of the 325m rows with any expiration date at all, ~235m of them are expired and we expect them to be deleted from the table.
- There are no other queries using
index_ci_job_artifacts_on_expire_at_and_job_id
, so we don't especially care about intermittent degradation of index scans on this particular index.
and realized that if we were using this index for this particular one-off operation of removing old expired artifacts with no locked status, this index wouldn't be of much use to anyone and is mostly filled with rows that don't eve have expire_at
, which is the indexed column. If we want to use this index for some reason, we should at least rebuild at as a partial index and restrict to rows that actually have expire_at
.
But more likely, once we're done clearing out the backlog of expired artifacts that we'd been previously unable to remove, we just drop this index right along with the worker that had been using it. The worker will stop doing work once the old artifact backlog is finished, and when that happens this index should also stop getting any queries.
Proposal
When we remove the Ci::UpdateUnknownLockedStatusArtifactsWorker
, we should at least consider revising and repurposing this index if not removing it entirely.
Double check all the dependencies related to this index:
- https://gitlab.com/gitlab-org/gitlab/-/blob/ad4816166236a1be6fd88451ab14f66a46594576/app/services/ci/job_artifacts/destroy_all_expired_service.rb#L31
- https://gitlab.com/gitlab-org/gitlab/-/blob/ad4816166236a1be6fd88451ab14f66a46594576/app/models/ci/job_artifact.rb#L182
- https://gitlab.com/gitlab-org/gitlab/-/blob/ad4816166236a1be6fd88451ab14f66a46594576/app/models/ci/job_artifact.rb#L155
- https://gitlab.com/gitlab-org/gitlab/-/blob/ad4816166236a1be6fd88451ab14f66a46594576/app/services/ci/job_artifacts/update_unknown_locked_status_service.rb#L73