ActiveRecord::QueryCanceled in Security::RelatedPipelinesFinder

Problem

Security::RelatedPipelinesFinder fails with statement timeout for projects with a large number of pipelines for a particular sha in the default branch. This could be possible for cases when a project has scheduled pipelines with a small interval resulting in a huge number of pipelines for a single commit.

Query

SELECT \"p_ci_pipelines\".* FROM \"p_ci_pipelines\" WHERE \"p_ci_pipelines\".\"project_id\" = $1 AND (\"p_ci_pipelines\".\"source\" IN ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11) OR \"p_ci_pipelines\".\"source\" IS NULL) AND \"p_ci_pipelines\".\"sha\" = $12 AND \"p_ci_pipelines\".\"ref\" = $13 ORDER BY \"p_ci_pipelines\".\"id\" DESC LIMIT $14"

This is possibly because of insufficient index that satisfies this query: we have an index on source (index_ci_pipelines_on_project_id_and_source) and an index on sha (index_ci_pipelines_on_project_id_and_sha), but postgres does not pick the right index for cases like this.

Sentry Issue: GITLABCOM-848A

PG::QueryCanceled: ERROR:  canceling statement due to statement timeout (PG::QueryCanceled)
        "ee/app/models/ee/merge_request.rb:718:in `find_pipeline_with_reports'",
        "ee/app/models/ee/merge_request.rb:693:in `find_base_pipeline_with_security_reports'",
        "ee/app/models/ee/merge_request.rb:683:in `find_common_ancestor_pipeline_with_security_reports'",
        "ee/app/models/ee/merge_request.rb:553:in `latest_scan_finding_comparison_pipeline'",
        "ee/app/services/concerns/security/scan_result_policies/related_pipelines.rb:42:in `target_pipeline_for_merge_request'",
        "ee/app/services/concerns/security/scan_result_policies/related_pipelines.rb:27:in `related_target_pipeline_ids_for_merge_request'",
        "ee/app/services/security/scan_result_policies/update_approvals_service.rb:189:in `related_target_pipeline_ids'",
...
(223 additional frame(s) were not displayed)

ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR:  canceling statement due to statement timeout (ActiveRecord::QueryCanceled)

• Logs