Query Performance Investigation - Query ID 1677199524406736335 (ci_builds and ci_pipelines tables)

Description

This query is frequently reported as top 10 in total time taken.

SELECT "ci_pipelines".*
FROM "ci_pipelines"
WHERE "ci_pipelines"."project_id" = $1 AND ("ci_pipelines"."source" IN ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) OR "ci_pipelines"."source" IS NULL) AND "ci_pipelines"."ref" = $13 AND "ci_pipelines"."id" NOT IN (
WITH RECURSIVE "base_and_descendants" AS
    (
    (SELECT "ci_pipelines".*
    FROM "ci_pipelines"
    WHERE "ci_pipelines"."id" = $14)
UNION
    (SELECT "ci_pipelines".*
    FROM "ci_pipelines", "base_and_descendants", "ci_sources_pipelines"
    WHERE "ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id" AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id" AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id"))
SELECT "id"
FROM "base_and_descendants" AS "ci_pipelines"
) AND "ci_pipelines"."sha" != $15 AND
("ci_pipelines"."status" IN
($16,$17,$18,$19,$20,$21)) AND
(NOT EXISTS
(SELECT "ci_builds".*
FROM "ci_builds" INNER JOIN "ci_builds_metadata" ON "ci_builds_metadata"."build_id" = "ci_builds"."id"
WHERE "ci_builds"."type" = $22 AND (ci_builds.commit_id = ci_pipelines.id) AND ("ci_builds"."status" IN ($23,$24,$25)) AND (ci_builds_metadata.id NOT IN (SELECT "ci_builds_metadata"."id"
    FROM "ci_builds_metadata"
    WHERE (ci_builds_metadata.build_id = ci_builds.id) AND "ci_builds_metadata"."interruptible" = $26)))
) ORDER BY "ci_pipelines"."id" ASC LIMIT $27 
--application:web,correlation_id:01EY8BF5ZGYHF44EHWCQQ1QHR6/

Requested Data points

Please provide as many of these fields as possible when submitting a query performance report.

• Source of calls (Sidekiq, WebAPI, etc) --application:web,correlation_id:01EY8BF5ZGYHF44EHWCQQ1QHR6/
• Query ID - 1677199524406736335
• % of Total time < 1% of time at .3 seconds during 1 minute of monitoring