Add pipeline-scoped projection to siphon_p_ci_stages for Job Analytics stage join pruning

Problem

After the stages-join fix (!243923 (merged)), the Job Analytics siphon read path (job_analytics_siphon) reads a flat ~17.2M rows for every 7-day query on gitlab-org/gitlab, of which ~15M rows are the stages side: the stage_name LEFT JOIN scans the project's entire stage history, pruned only by traversal_path (7,483 of 2.66M granules). The legacy ci_finished_builds path reads 0.1–2M rows for the same queries.

The stages scan is now the dominant term for default (7-day) views. Production measurements (2026-07-08, peek):

Scenario (7d) Legacy rows_read Siphon rows_read
Default view 1.9M 17.2M
Source=MR event (0 results) 0.13M 17.2M

Why the naive fix doesn't work

Scoping the stages subquery by pipeline_id IN (SELECT id FROM pipelines) was measured on production: granules stay ~7,483 → 7,491 because pipeline_id is not in the stages sort key (traversal_path, id); the filter applies post-read, and the extra pipelines-CTE evaluation made it a net regression (3.3s → 3.9s on 90d).

Proposed solution

Add a projection on siphon_p_ci_stages sorted so a pipelines IN-set can prune granules:

ALTER TABLE siphon_p_ci_stages ADD PROJECTION by_traversal_path_pipeline_id (
    SELECT id, partition_id, traversal_path, pipeline_id, name,
           _siphon_replicated_at, _siphon_deleted
    ORDER BY traversal_path, pipeline_id, id, partition_id
);

Then scope the stages subquery in SiphonFinishedBuildsFinder#stage_join by the pipelines subquery (pipeline_id IN (...)), keeping the join on id. IN-set granule pruning against a sort-key column is proven in production (the pre-!243923 query pruned stages to 775 granules via an id IN-set — its problem was the set's source, a second builds-CTE evaluation, not the pruning mechanism). pipeline_id is immutable per stage row, so pre-dedup filtering is dedup-safe.

Considerations

  • The stages scoping only applies when a pipelines subquery exists (ref/source filter present; the UI always sends branch). Callers without it keep the current traversal_path-only scope, or we derive a pipelines set from the date window.
  • Second projection on a CDC table: with deduplicate_merge_projection_mode = 'rebuild', every merge rebuilds projection parts → measure merge amplification before/after on staging.
  • MATERIALIZE PROJECTION backfill mutation required for existing parts.
  • Estimated win: stages side ~15M → ~1–2M rows on 7d default views; combined with the builds filtered projection (separate issue) brings siphon rows_read near legacy levels.

References