Prune security_findings partition fanout in descendant-pipeline vulnerability check

What

Follow-up to !252311 (merged) (INC-13566 / corrective action gitlab-com/gl-infra/production-engineering#29649). Adds partition pruning to Ci::Pipeline#has_security_findings_in_self_and_descendants?.

Because this check spans self + descendant pipelines — which can live in different partitions — it can't use the single-partition accessor. Instead it collects the distinct findings_partition_numbers from the pipelines' scans and filters security_findings by that set (partition_number IN (...)), enabling multi-partition pruning. Mirrors the existing pattern in Security::FindingsFinder.

Why pruning by the scans' partition set is correct

Summary: the partition set derived from the scans is guaranteed to cover every partition the findings live in, so the IN (...) filter can never drop a real finding. Details below.

Full correctness reasoning

• Every finding is written with partition_number = its scan's findings_partition_number (store_findings_service.rb:76), and partition_number is attr_readonly — it can never change after insert.
• A finding belongs_to :scan with optional: false, so a finding cannot exist without a scan.
• Therefore distinct(findings_partition_number) over the scans is a superset of the partitions the findings occupy: it can only ever over-include an empty partition (harmless), never under-include (which would drop findings).
• Empty-set guard: if there are no scans anywhere in the tree, the filter is skipped and the original query runs — safe, and there are no findings to find anyway.

Query plans (postgres.ai, gitlab-production-sec)

Captured against the sec DB (security_findings / security_scans).

Before (no partition_number predicate) — fans out across 66 partitions:

EXPLAIN — Append over 66 partitions, planning 27.55ms / total 31.5ms

Limit  (cost=1.14..326.11 rows=1 width=4)
  ->  Nested Loop  (cost=1.14..1626.00 rows=5 width=4)
        ->  Index Scan using index_security_scans_on_length_of_warnings on security_scans
              Index Cond: (pipeline_id = '2796061674'::bigint)
              Filter: ((status = 1) AND (project_id = 278964))
        ->  Append  (cost=0.57..1261.09 rows=35472 width=8)
              ->  Index Only Scan ... security_findings_322 ...
              ->  Index Only Scan ... security_findings_323 ...
              ... (66 partitions total, security_findings_322 .. _354+) ...
Settings: work_mem='100MB', random_page_cost='1.5', seq_page_cost='4', effective_cache_size='338688MB', jit='off'
planning: 27.552 ms, execution: 3.983 ms, total: 31.5 ms

After (partition_number IN (350)) — prunes to 1 partition:

EXPLAIN — single-partition scan, planning 4.11ms / total 4.18ms

Limit
  ->  Nested Loop
        ->  Index Scan using index_security_scans_on_length_of_warnings on security_scans
              Index Cond: (security_scans.pipeline_id = '2796061674'::bigint)
              Filter: ((security_scans.status = 1) AND (security_scans.project_id = 278964))
        ->  Index Scan using security_findings_350_scan_id_deduplicated_idx on security_findings_350
              Index Cond: (security_findings.scan_id = security_scans.id)
              Filter: (security_findings.partition_number = 350)
planning: 4.110 ms, execution: 0.071 ms, total: 4.18 ms, shared hit=4

Net: 66 partitions → 1, planning ~7x lower, total ~31.5ms → ~4.2ms. (A descendant tree spanning multiple partitions prunes to that small set rather than all 66.)

Feature flag

Gated behind its own flag prune_security_findings_partition_descendants (default off), separate from prune_security_findings_partition (!252311 (merged)), so the two are independently mergeable.

Testing

ee/spec/models/ci/pipeline_spec.rb: FF on/off, plus a multi-partition case asserting the emitted SQL carries partition_number IN (...) (on) and not (off). Verified locally green (6 examples, 0 failures).

Closes part of gitlab-com/gl-infra/production-engineering#29649.