Loading
Scope policy related pipelines to the merge request
What does this MR do and why?
Approval policies no longer pick up another merge request's pipeline when two merge requests share a head SHA.
Details
When two merge requests share a head SHA, Security::RelatedPipelinesFinder picked the latest pipeline per source at that SHA across the whole project. The newer merge request pipeline of the other MR replaced the MR's own pipeline. Approval policies then compared the wrong pipeline's scans and reported "Scanner removed by MR", although the MR's own pipeline ran those scanners. Retrying jobs did not help.
This MR excludes pipelines that belong to a different merge request. It keeps pipelines with no merge request, plus the evaluated MR's.
Security::ScanResultPolicies::RelatedPipelines(the policy concern) now passes itsmerge_requestto the finder.- Callers without one (the MR security widget, the enabled-scans GraphQL resolver) fall back to the pipeline's own
merge_request_id. - Behind the
gitlab_com_deriskflagscope_security_related_pipelines_to_merge_request(default off). - No changelog: default-off flag.
References
Screenshots or screen recordings
Not applicable, backend only.
How to set up and validate locally
- In rails console, enable the feature flag:
Feature.enable(:scope_security_related_pipelines_to_merge_request) - In a project with a
scan_findingapproval policy, open MR A (feature to main) and MR B (feature to another branch) from the same branch. - Add CI rules so secret detection and dependency scanning run only for MRs targeting main.
- Let MR B's pipeline finish after MR A's.
- Check MR A. With the flag on, it has no "Scanner removed by MR" violation. With the flag off, it does.
Specs:
bin/rspec ee/spec/finders/security/related_pipelines_finder_spec.rb ee/spec/services/concerns/security/scan_result_policies/related_pipelines_spec.rbDatabase
The change adds ("p_ci_pipelines"."merge_request_id" = <mr_id> OR "p_ci_pipelines"."merge_request_id" IS NULL) inside the existing inner subquery of Ci::Pipeline.latest_limited_pipeline_ids_per_source.
The plan is the same before and after. The new predicate is only a Filter on rows the index already found.
Evidence and query
Plan on a production-size clone (postgres.ai, CI database), for a SHA with 88 pipelines:
-
Same plan shape before and after: index scans on
index_ci_pipelines_on_project_id_and_shaand the per-partitionproject_id_shaindexes. -
Buffers identical (155 shared hits).
-
Execution about 1 ms before and after.
-
Re-run on 2026-10-13 with a SHA that has no completed pipelines (0 rows returned): identical plan shape before and after, 67 buffers touched in both (the "after" run was cold: 6 hit + 61 read, 83 ms; the "before" run was warm: 67 hit, 0.7 ms).
-
Full plans on production-size data, re-run on 2026-10-14 (postgres.ai Database Lab,
gitlab-production-ci, project 278964, a SHA with 15 pipelines, 2 rows returned). The Joe CLI returns plan text and command ids but no shareable console link, so the complete plans for every partition are pasted below instead.- Before (master, Joe command 521954) and after (this MR, Joe command 521950), warm: identical plan shape, 126 shared buffer hits in both, execution 0.7 ms in both.
- Cold first runs: before 119.7 ms (6 hit + 120 read), after 0.7 ms (all hits, cache already warmed by the "before" run). The difference is cache state, not the predicate.
Before: full EXPLAIN (ANALYZE, BUFFERS), Joe command 521954 (I/O timing lines of 0 removed)
SELECT DISTINCT ON (source) id FROM (SELECT "p_ci_pipelines".* FROM "p_ci_pipelines" WHERE "p_ci_pipelines"."project_id" = 278964 AND "p_ci_pipelines"."tag" = FALSE AND ("p_ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 15, 18) OR "p_ci_pipelines"."source" IS NULL) AND "p_ci_pipelines"."status" IN ('success', 'failed', 'canceled', 'skipped', 'manual') AND "p_ci_pipelines"."sha" = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32' ORDER BY "p_ci_pipelines"."id" DESC LIMIT 1000) AS recent_pipelines ORDER BY source, id DESCUnique (cost=55.50..55.58 rows=16 width=12) (actual time=0.373..0.377 rows=2 loops=1)
Buffers: shared hit=126
-> Sort (cost=55.50..55.54 rows=16 width=12) (actual time=0.372..0.375 rows=2 loops=1)
Sort Key: recent_pipelines.source, recent_pipelines.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=126
-> Subquery Scan on recent_pipelines (cost=54.98..55.18 rows=16 width=12) (actual time=0.333..0.336 rows=2 loops=1)
Buffers: shared hit=120
-> Limit (cost=54.98..55.02 rows=16 width=1816) (actual time=0.331..0.334 rows=2 loops=1)
Buffers: shared hit=120
-> Sort (cost=54.98..55.02 rows=16 width=1816) (actual time=0.330..0.332 rows=2 loops=1)
Sort Key: p_ci_pipelines.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=120
-> Append (cost=0.73..54.66 rows=16 width=1816) (actual time=0.268..0.323 rows=2 loops=1)
Buffers: shared hit=120
-> Index Scan using index_ci_pipelines_on_project_id_and_sha on gitlab_partitions_dynamic.ci_pipelines p_ci_pipelines_1 (cost=0.73..3.76 rows=1 width=1816) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_1.project_id = 278964) AND ((p_ci_pipelines_1.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_1.tag) AND ((p_ci_pipelines_1.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_1.source IS NULL)) AND ((p_ci_pipelines_1.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=5
-> Index Scan using ci_pipelines_103_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_103 p_ci_pipelines_2 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.018..0.018 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_2.project_id = 278964) AND ((p_ci_pipelines_2.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_2.tag) AND ((p_ci_pipelines_2.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_2.source IS NULL)) AND ((p_ci_pipelines_2.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_104_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_104 p_ci_pipelines_3 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_3.project_id = 278964) AND ((p_ci_pipelines_3.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_3.tag) AND ((p_ci_pipelines_3.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_3.source IS NULL)) AND ((p_ci_pipelines_3.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_105_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_105 p_ci_pipelines_4 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_4.project_id = 278964) AND ((p_ci_pipelines_4.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_4.tag) AND ((p_ci_pipelines_4.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_4.source IS NULL)) AND ((p_ci_pipelines_4.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_106_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_106 p_ci_pipelines_5 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_5.project_id = 278964) AND ((p_ci_pipelines_5.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_5.tag) AND ((p_ci_pipelines_5.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_5.source IS NULL)) AND ((p_ci_pipelines_5.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_107_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_107 p_ci_pipelines_6 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_6.project_id = 278964) AND ((p_ci_pipelines_6.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_6.tag) AND ((p_ci_pipelines_6.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_6.source IS NULL)) AND ((p_ci_pipelines_6.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_108_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_108 p_ci_pipelines_7 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_7.project_id = 278964) AND ((p_ci_pipelines_7.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_7.tag) AND ((p_ci_pipelines_7.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_7.source IS NULL)) AND ((p_ci_pipelines_7.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_109_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_109 p_ci_pipelines_8 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_8.project_id = 278964) AND ((p_ci_pipelines_8.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_8.tag) AND ((p_ci_pipelines_8.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_8.source IS NULL)) AND ((p_ci_pipelines_8.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_110_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_110 p_ci_pipelines_9 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_9.project_id = 278964) AND ((p_ci_pipelines_9.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_9.tag) AND ((p_ci_pipelines_9.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_9.source IS NULL)) AND ((p_ci_pipelines_9.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_111_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_111 p_ci_pipelines_10 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_10.project_id = 278964) AND ((p_ci_pipelines_10.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_10.tag) AND ((p_ci_pipelines_10.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_10.source IS NULL)) AND ((p_ci_pipelines_10.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_112_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_112 p_ci_pipelines_11 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_11.project_id = 278964) AND ((p_ci_pipelines_11.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_11.tag) AND ((p_ci_pipelines_11.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_11.source IS NULL)) AND ((p_ci_pipelines_11.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_113_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_113 p_ci_pipelines_12 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_12.project_id = 278964) AND ((p_ci_pipelines_12.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_12.tag) AND ((p_ci_pipelines_12.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_12.source IS NULL)) AND ((p_ci_pipelines_12.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_114_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_114 p_ci_pipelines_13 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_13.project_id = 278964) AND ((p_ci_pipelines_13.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_13.tag) AND ((p_ci_pipelines_13.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_13.source IS NULL)) AND ((p_ci_pipelines_13.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_115_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_115 p_ci_pipelines_14 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_14.project_id = 278964) AND ((p_ci_pipelines_14.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_14.tag) AND ((p_ci_pipelines_14.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_14.source IS NULL)) AND ((p_ci_pipelines_14.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_116_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_116 p_ci_pipelines_15 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.063..0.108 rows=2 loops=1)
Index Cond: ((p_ci_pipelines_15.project_id = 278964) AND ((p_ci_pipelines_15.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_15.tag) AND ((p_ci_pipelines_15.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_15.source IS NULL)) AND ((p_ci_pipelines_15.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Rows Removed by Filter: 57
Buffers: shared hit=63
-> Seq Scan on gitlab_partitions_dynamic.ci_pipelines_117 p_ci_pipelines_16 (cost=0.03..0.03 rows=1 width=1816) (actual time=0.007..0.007 rows=0 loops=1)
Filter: ((NOT p_ci_pipelines_16.tag) AND (p_ci_pipelines_16.project_id = 278964) AND ((p_ci_pipelines_16.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text) AND ((p_ci_pipelines_16.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_16.source IS NULL)) AND ((p_ci_pipelines_16.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Settings: seq_page_cost = '4', work_mem = '100MB', jit = 'off', random_page_cost = '1.5', effective_cache_size = '338688MB'
Query ID: -6225265449552314002After: full EXPLAIN (ANALYZE, BUFFERS), Joe command 521950 (I/O timing lines of 0 removed)
SELECT DISTINCT ON (source) id FROM (SELECT "p_ci_pipelines".* FROM "p_ci_pipelines" WHERE "p_ci_pipelines"."project_id" = 278964 AND "p_ci_pipelines"."tag" = FALSE AND ("p_ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 15, 18) OR "p_ci_pipelines"."source" IS NULL) AND ("p_ci_pipelines"."merge_request_id" = 547786251 OR "p_ci_pipelines"."merge_request_id" IS NULL) AND "p_ci_pipelines"."status" IN ('success', 'failed', 'canceled', 'skipped', 'manual') AND "p_ci_pipelines"."sha" = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32' ORDER BY "p_ci_pipelines"."id" DESC LIMIT 1000) AS recent_pipelines ORDER BY source, id DESCUnique (cost=55.54..55.62 rows=16 width=12) (actual time=0.383..0.387 rows=2 loops=1)
Buffers: shared hit=126
-> Sort (cost=55.54..55.58 rows=16 width=12) (actual time=0.382..0.384 rows=2 loops=1)
Sort Key: recent_pipelines.source, recent_pipelines.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=126
-> Subquery Scan on recent_pipelines (cost=55.02..55.22 rows=16 width=12) (actual time=0.349..0.353 rows=2 loops=1)
Buffers: shared hit=120
-> Limit (cost=55.02..55.06 rows=16 width=1816) (actual time=0.348..0.350 rows=2 loops=1)
Buffers: shared hit=120
-> Sort (cost=55.02..55.06 rows=16 width=1816) (actual time=0.347..0.349 rows=2 loops=1)
Sort Key: p_ci_pipelines.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=120
-> Append (cost=0.73..54.70 rows=16 width=1816) (actual time=0.288..0.336 rows=2 loops=1)
Buffers: shared hit=120
-> Index Scan using index_ci_pipelines_on_project_id_and_sha on gitlab_partitions_dynamic.ci_pipelines p_ci_pipelines_1 (cost=0.73..3.77 rows=1 width=1816) (actual time=0.021..0.021 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_1.project_id = 278964) AND ((p_ci_pipelines_1.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_1.tag) AND ((p_ci_pipelines_1.merge_request_id = 547786251) OR (p_ci_pipelines_1.merge_request_id IS NULL)) AND ((p_ci_pipelines_1.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_1.source IS NULL)) AND ((p_ci_pipelines_1.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=5
-> Index Scan using ci_pipelines_103_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_103 p_ci_pipelines_2 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.018..0.018 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_2.project_id = 278964) AND ((p_ci_pipelines_2.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_2.tag) AND ((p_ci_pipelines_2.merge_request_id = 547786251) OR (p_ci_pipelines_2.merge_request_id IS NULL)) AND ((p_ci_pipelines_2.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_2.source IS NULL)) AND ((p_ci_pipelines_2.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_104_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_104 p_ci_pipelines_3 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_3.project_id = 278964) AND ((p_ci_pipelines_3.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_3.tag) AND ((p_ci_pipelines_3.merge_request_id = 547786251) OR (p_ci_pipelines_3.merge_request_id IS NULL)) AND ((p_ci_pipelines_3.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_3.source IS NULL)) AND ((p_ci_pipelines_3.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_105_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_105 p_ci_pipelines_4 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_4.project_id = 278964) AND ((p_ci_pipelines_4.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_4.tag) AND ((p_ci_pipelines_4.merge_request_id = 547786251) OR (p_ci_pipelines_4.merge_request_id IS NULL)) AND ((p_ci_pipelines_4.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_4.source IS NULL)) AND ((p_ci_pipelines_4.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_106_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_106 p_ci_pipelines_5 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_5.project_id = 278964) AND ((p_ci_pipelines_5.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_5.tag) AND ((p_ci_pipelines_5.merge_request_id = 547786251) OR (p_ci_pipelines_5.merge_request_id IS NULL)) AND ((p_ci_pipelines_5.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_5.source IS NULL)) AND ((p_ci_pipelines_5.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_107_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_107 p_ci_pipelines_6 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_6.project_id = 278964) AND ((p_ci_pipelines_6.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_6.tag) AND ((p_ci_pipelines_6.merge_request_id = 547786251) OR (p_ci_pipelines_6.merge_request_id IS NULL)) AND ((p_ci_pipelines_6.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_6.source IS NULL)) AND ((p_ci_pipelines_6.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_108_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_108 p_ci_pipelines_7 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_7.project_id = 278964) AND ((p_ci_pipelines_7.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_7.tag) AND ((p_ci_pipelines_7.merge_request_id = 547786251) OR (p_ci_pipelines_7.merge_request_id IS NULL)) AND ((p_ci_pipelines_7.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_7.source IS NULL)) AND ((p_ci_pipelines_7.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_109_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_109 p_ci_pipelines_8 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_8.project_id = 278964) AND ((p_ci_pipelines_8.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_8.tag) AND ((p_ci_pipelines_8.merge_request_id = 547786251) OR (p_ci_pipelines_8.merge_request_id IS NULL)) AND ((p_ci_pipelines_8.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_8.source IS NULL)) AND ((p_ci_pipelines_8.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_110_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_110 p_ci_pipelines_9 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_9.project_id = 278964) AND ((p_ci_pipelines_9.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_9.tag) AND ((p_ci_pipelines_9.merge_request_id = 547786251) OR (p_ci_pipelines_9.merge_request_id IS NULL)) AND ((p_ci_pipelines_9.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_9.source IS NULL)) AND ((p_ci_pipelines_9.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_111_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_111 p_ci_pipelines_10 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_10.project_id = 278964) AND ((p_ci_pipelines_10.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_10.tag) AND ((p_ci_pipelines_10.merge_request_id = 547786251) OR (p_ci_pipelines_10.merge_request_id IS NULL)) AND ((p_ci_pipelines_10.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_10.source IS NULL)) AND ((p_ci_pipelines_10.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_112_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_112 p_ci_pipelines_11 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_11.project_id = 278964) AND ((p_ci_pipelines_11.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_11.tag) AND ((p_ci_pipelines_11.merge_request_id = 547786251) OR (p_ci_pipelines_11.merge_request_id IS NULL)) AND ((p_ci_pipelines_11.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_11.source IS NULL)) AND ((p_ci_pipelines_11.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_113_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_113 p_ci_pipelines_12 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_12.project_id = 278964) AND ((p_ci_pipelines_12.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_12.tag) AND ((p_ci_pipelines_12.merge_request_id = 547786251) OR (p_ci_pipelines_12.merge_request_id IS NULL)) AND ((p_ci_pipelines_12.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_12.source IS NULL)) AND ((p_ci_pipelines_12.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_114_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_114 p_ci_pipelines_13 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_13.project_id = 278964) AND ((p_ci_pipelines_13.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_13.tag) AND ((p_ci_pipelines_13.merge_request_id = 547786251) OR (p_ci_pipelines_13.merge_request_id IS NULL)) AND ((p_ci_pipelines_13.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_13.source IS NULL)) AND ((p_ci_pipelines_13.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_115_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_115 p_ci_pipelines_14 (cost=0.60..3.63 rows=1 width=1816) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((p_ci_pipelines_14.project_id = 278964) AND ((p_ci_pipelines_14.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_14.tag) AND ((p_ci_pipelines_14.merge_request_id = 547786251) OR (p_ci_pipelines_14.merge_request_id IS NULL)) AND ((p_ci_pipelines_14.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_14.source IS NULL)) AND ((p_ci_pipelines_14.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Buffers: shared hit=4
-> Index Scan using ci_pipelines_116_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_116 p_ci_pipelines_15 (cost=0.59..3.63 rows=1 width=1816) (actual time=0.078..0.116 rows=2 loops=1)
Index Cond: ((p_ci_pipelines_15.project_id = 278964) AND ((p_ci_pipelines_15.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text))
Filter: ((NOT p_ci_pipelines_15.tag) AND ((p_ci_pipelines_15.merge_request_id = 547786251) OR (p_ci_pipelines_15.merge_request_id IS NULL)) AND ((p_ci_pipelines_15.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_15.source IS NULL)) AND ((p_ci_pipelines_15.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Rows Removed by Filter: 57
Buffers: shared hit=63
-> Seq Scan on gitlab_partitions_dynamic.ci_pipelines_117 p_ci_pipelines_16 (cost=0.03..0.03 rows=1 width=1816) (actual time=0.008..0.008 rows=0 loops=1)
Filter: ((NOT p_ci_pipelines_16.tag) AND ((p_ci_pipelines_16.merge_request_id = 547786251) OR (p_ci_pipelines_16.merge_request_id IS NULL)) AND (p_ci_pipelines_16.project_id = 278964) AND ((p_ci_pipelines_16.sha)::text = 'cb16354aa2f0f3a61a4006a607bd4acb9dc5fd32'::text) AND ((p_ci_pipelines_16.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15,18}'::integer[])) OR (p_ci_pipelines_16.source IS NULL)) AND ((p_ci_pipelines_16.status)::text = ANY ('{success,failed,canceled,skipped,manual}'::text[])))
Settings: effective_cache_size = '338688MB', seq_page_cost = '4', work_mem = '100MB', jit = 'off', random_page_cost = '1.5'
Query ID: -5385922613214449706Plan after the change (EXPLAIN (ANALYZE, BUFFERS), abridged; the same node repeats for every partition, 103 to 116):
Unique
-> Sort (Sort Key: source, id DESC)
-> Subquery Scan on recent_pipelines
-> Limit
-> Sort (Sort Key: id DESC)
-> Append
-> Index Scan using index_ci_pipelines_on_project_id_and_sha on ci_pipelines
Index Cond: (project_id = <project_id> AND sha = '<sha>')
Filter: (NOT tag AND (merge_request_id = <mr_id> OR merge_request_id IS NULL)
AND (source = ANY (...) OR source IS NULL) AND status = ANY (...))
-> Index Scan using ci_pipelines_<n>_project_id_sha_idx on ci_pipelines_<n> (x14 partitions)
(same Index Cond and Filter)
-> Seq Scan on ci_pipelines_117 (empty partition)SELECT DISTINCT ON (source) id FROM (SELECT "p_ci_pipelines".* FROM "p_ci_pipelines" WHERE "p_ci_pipelines"."project_id" = <project_id> AND "p_ci_pipelines"."tag" = FALSE AND ("p_ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 15, 18) OR "p_ci_pipelines"."source" IS NULL) AND ("p_ci_pipelines"."merge_request_id" = <mr_id> OR "p_ci_pipelines"."merge_request_id" IS NULL) AND "p_ci_pipelines"."status" IN ('success', 'failed', 'canceled', 'skipped', 'manual') AND "p_ci_pipelines"."sha" = '<sha>' ORDER BY "p_ci_pipelines"."id" DESC LIMIT 1000) AS recent_pipelines ORDER BY source, id DESCMR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Reviewable LOC: 30 (plus 106 in tests)
Context for LLM agents
Rejected alternatives:
- Filtering by ref on the source side: rejected. Branch pipelines and MR pipelines of the same MR have different refs and are meant to be combined.
- Filtering in each caller: rejected. The finder is the shared point, so every policy caller gets the fix.
- Threading the merge request through the security widget's comparison service: deferred. It changes reactive-cache keys. The widget is scoped only when its head pipeline is an MR pipeline.
Invariants:
- Pipelines with
merge_request_idNULL must stay in the set. - Target-side lookups (
related_target_pipeline_ids_for_merge_request) are unaffected. They filter by target branch ref, and target pipelines are branch pipelines.
Owed elsewhere
- The security widget is still unscoped when the MR's head pipeline is a branch pipeline: #633586
Edited by Alan (Maciej) Paruszewski