<details><summary>A query counting ci builds in the CreatePipelineWorker</summary> ```sql /*application:sidekiq,correlation_id:af6af5c2ae45fde39bda7a8f1bf8bd3c,jid:b6853eb34237f7483ea09f77,endpoint_id:Security::ScanExecutionPolicies::CreatePipelineWorker,db_config_database:gitlabhq_production_sidekiq,db_config_name:ci_replica*/ SELECT COUNT(*) FROM (SELECT $1 AS one FROM "p_ci_builds" INNER JOIN "p_ci_pipelines" "pipeline" ON "pipeline"."partition_id" IS NOT NULL AND "pipeline"."id" = "p_ci_builds"."commit_id" AND "pipeline"."partition_id" = "p_ci_builds"."partition_id" WHERE "p_ci_builds"."type" = $2 AND "pipeline"."source" = $3 AND "p_ci_builds"."project_id" IN ($4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120, $121, $122, $123, $124, $125, $126, $127, $128, $129, $130, $131, $132, $133, $134, $135, $136, $137, $138, $139, $140, $141, $142, $143, $144, $145, $146, $147, $148, $149, $150, $151, $152, $153, $154, $155, $156, $157, $158, $159, $160, $161, $162, $163, $164, $165, $166, $167, $168, $169, $170, $171, $172, $173, $174, $175, $176, $177, $178, $179, $180, $181, $182, $183, $184, $185, $186, $187, $188, $189, $190, $191, $192, $193, $194, $195, $196, $197, $198, $199, $200, $201, $202, $203, $204, $205, $206, $207, $208, $209, $210, $211, $212, $213, $214, $215, $216, $217, $218, $219, $220, $221, $222, $223, $224, $225, $226, $227, $228, $229, $230, $231, $232, $233, $234, $235, $236, $237, $238, $239, $240, $241, $242, $243, $244, $245, $246, $247, $248, $249, $250, $251, $252, $253) AND ("p_ci_builds"."status" IN ($254,$255,$256,$257,$258,$259,$260)) AND "p_ci_builds"."created_at" > $261 AND "p_ci_builds"."updated_at" > $262 LIMIT $263) subquery_for_count ``` </details> (fingerprint a957327554721142, queryid -4132042013129971627, called from https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/app/workers/security/scan_execution_policies/create_pipeline_worker.rb#L79-79) abruptly started consuming more resources than all the rest of the query workload on gitlab.com's CI replica databases on `2024-11-13`. All graph sources in https://dashboards.gitlab.net/goto/HgegQinHR?orgId=1.  The above shows two different metrics. The top red line is the total time spent in queries (in seconds per second of wall time) on CI replicas. The lower yellow line tracking the same peaks is the time spent only in the problem query discussed here.  As an alternative view, this graphs the percentage of the total CPU workload captured by this query. We can see that it peaks at over 70% of the workload for CI replicas. This query should be improved, or the change should be reverted, to improve its performance.