Add partial index to ci_pipelines for unlocked condition
What does this MR do and why?
Ci::PipelineArtifacts::ExpireArtifactsWorker has been hitting
statement timeouts on GitLab.com. The SQL query to find these expired
artifacts currently does an expensive JOIN due to a nested loop
between one large table (ci_pipeline_artifacts) and one very large
table (ci_pipelines). In the latter table, PostgreSQL has to filter
out locked pipelines, but on GitLab.com it has to iterate through over
3 million rows.
Adding a partial index on the locked = 0 condition helps reduce that
table to a much smaller number. We still need to modify the query to
work on smaller batches, but that will be addressed in
!96945 (closed).
Relates to #372500 (closed)
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.