Optimize SQL queries in Ci::PipelineCreation::CancelRedundantPipelinesService

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

Context

Identified in #599037 as part of CI SQL query optimization work. The queries in CancelRedundantPipelinesService lack partition_id filters, preventing partition pruning and causing unnecessary cross-partition scans.

Problem

Query 1 — cancelable_status_pipeline_pks (line 39)

# app/services/ci/pipeline_creation/cancel_redundant_pipelines_service.rb#L39
project.all_pipelines
  .for_ref(pipeline.ref)
  .id_not_in(pipeline.id)
  .with_status(Ci::Pipeline::CANCELABLE_STATUSES)
  .order_id_desc
  .limit(MAX_CANCELLATIONS_PER_PIPELINE)
  .pluck_primary_key

This query scans across all partitions without a partition_id filter. Cancelable pipelines are very likely to be in the same partition as the new pipeline, or at most in [pipeline.partition_id, pipeline.partition_id - 1]. We should investigate whether we can safely scope this query to a narrow set of partition IDs to enable partition pruning.

Query 2 — conservative_cancelable_pipeline_pks (line 58)

# app/services/ci/pipeline_creation/cancel_redundant_pipelines_service.rb#L58
Ci::Pipeline.primary_key_in(pks_batch).conservative_interruptible

After plucking only id values in Query 1, this re-queries pipelines by id alone without partition_id, again preventing partition pruning. The fix is to pluck both [:id, :partition_id] in Query 1 and feed them back as a composite key lookup:

where([:id, :partition_id] => values)

Proposal

  1. In cancelable_status_pipeline_pks, pluck [:id, :partition_id] instead of just the primary key, and scope the initial query to partition_id IN [pipeline.partition_id, pipeline.partition_id - 1] (with a safe fallback if that assumption proves incorrect).
  2. In conservative_cancelable_pipeline_pks, use the (id, partition_id) pairs from step 1 to build a composite-key WHERE clause, enabling partition pruning.

References

Edited by 🤖 GitLab Bot 🤖