Skip to content

Use status scope for CancelRedundantPipelinesService

Max Fan requested to merge 414833-improve-cancel-redundant-pipeline-query into master

What does this MR do and why?

Currently this service has really slow postgres queries.

This MR attempts to optimize the query by adding another WHERE status condition, thereby allowing postgres to use

Index Scan using index_ci_pipelines_on_project_id_and_status_and_created_at ✅

which is more efficient and removes less rows than the previous

Index Scan using index_ci_pipelines_on_project_idandrefandiddesc ❌

Postgres.ai (BEFORE): https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/20822/commands/68319

Postgres.ai (AFTER): https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/20926/commands/68496

There is no actual change in logic, as the with_status filters all statuses.

Full discussion in issue: #414833 (closed)

Screenshots or screen recordings

N/A

Before After

How to set up and validate locally

CancelRedundantPipelines should still work as normal.

The query change can be confirmed by copying the code in cancel_redundant_pipelines_service.rb and analyzing by doing parent_and_child_pipelines.to_sql

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #414833 (closed)

Edited by Max Fan

Merge request reports