Query used by CancelRedundantPipelinesService can cause high CPU load for database server if project has a large number of pipelines

CancelRedundantPipelinesService uses a database query to find all pipelines created before a certain date. When project has a large number of pipelines, this query adds CPU load on the database server.

SELECT "ci_pipelines"."id" FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = $1 AND "ci_pipelines"."created_at" > $2 ORDER BY "ci_pipelines"."status" ASC, "ci_pipelines"."created_at" ASC LIMIT $3 OFFSET $4

Reading the source code further, I saw this comment that includes a link to a production incident. Per this comment, maybe the Auto-cancel redundant pipelines option should not be enabled by default or perhaps the query can be improved to look for less records.

Workaround

Disable Auto cancel redundant pipelines setting for affected projects.