Scope siphon stages join by container only (!243923) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Fixes a ~6x performance regression in the Job Analytics siphon read path (job_analytics_siphon feature flag) for wide date windows, found during production verification on gitlab-org/gitlab.

SiphonFinishedBuildsFinder#stage_join scoped the stages subquery with id IN (SELECT stage_id FROM finished_builds) — a second reference to the builds CTE. ClickHouse inlines CTEs at every reference site, so the entire builds argMax-dedup aggregation executed twice, and the resulting CreatingSets step (a ~157K-element id set on a 90-day window) blocked the main scan behind the duplicate evaluation.

Measured on production ClickHouse (gitlab-org/gitlab, 90-day window):

Query shape	Duration	rows_read
Before (CTE referenced twice)	20.0s	43.3M (≈ 2 × 21M)
After (stages scoped by `traversal_path` only)	3.3s	36.2M

The stages side now scans the container's stage history instead of an id-scoped subset, but it reads only two narrow columns pruned by the traversal_path primary-key prefix — far cheaper than evaluating the builds dedup twice. EXPLAIN confirms the builds CTE is evaluated once and the by_traversal_path_started_at projection still prunes granules precisely.

SiphonStagesFinder#for_ids is removed as dead code (this was its only caller).

References

Feature issue: #601321 (closed) (verification + root-cause analysis in the issue thread)
Rollout issue: #604585 (closed)

Database changes

No schema changes. Emitted SQL changes for the stage_name join path only: the stages subquery drops its id IN (SELECT ...) predicate and keeps the traversal_path scope. Asserted in spec/lib/click_house/finders/ci/siphon_finished_builds_finder_spec.rb, including a regression test that the stages subquery never references the builds CTE.

Screenshots or screen recordings

N/A — no UI changes.

How to set up and validate locally

Prerequisites:

Start ClickHouse: gdk start clickhouse
Apply ClickHouse migrations: bundle exec rake gitlab:clickhouse:migrate

Copy the script below into a local file (for example /tmp/verify_stages_join_opt.rb) and run it from your GDK gitlab/ directory:

bundle exec rails runner /tmp/verify_stages_join_opt.rb

The script checks the emitted SQL shape (no second CTE reference, traversal_path scope intact), then seeds a throwaway project + builds + stage into the siphon_p_ci_* tables and verifies stage_name still resolves through the join, including that a same-id stage from another project is excluded. It cleans up in an ensure block. Compare each printed result against its EXPECT: line.

verify_stages_join_opt.rb

# frozen_string_literal: true

# Validation for the stages-join optimization in SiphonFinishedBuildsFinder.
# Usage: bundle exec rails runner /tmp/verify_stages_join_opt.rb

require 'securerandom'

def section(t) = puts("\n#{'=' * 72}\n#{t}\n#{'=' * 72}")

def quote(v)
  case v
  when nil then 'NULL'
  when Time, ActiveSupport::TimeWithZone then "'#{v.utc.strftime('%Y-%m-%d %H:%M:%S.%6N')}'"
  when true then '1'
  when false then '0'
  when Numeric then v.to_s
  else "'#{v.to_s.gsub("'", "\\\\'")}'"
  end
end

def insert(table, rows)
  return if rows.empty?

  cols = rows.first.keys
  vals = rows.map { |r| "(#{cols.map { |c| quote(r[c]) }.join(', ')})" }.join(', ')
  ClickHouse::Client.execute("INSERT INTO #{table} (#{cols.join(', ')}) VALUES #{vals}", :main)
end

uid = SecureRandom.hex(4)
base = Time.current.utc.beginning_of_minute
finder = ClickHouse::Finders::Ci::SiphonFinishedBuildsFinder
project = nil

begin
  section '1. SQL shape: stages subquery no longer references the builds CTE'
  ns = FactoryBot.create(:namespace, path: "sjopt-#{uid}")
  project = FactoryBot.create(:project, namespace: ns, path: "sjopt-#{uid}")
  sql = finder.for_container(project).with_stages(project).select(:name, :stage_name).mean_duration.to_sql
  puts "second CTE reference present: #{sql.include?('IN (SELECT `finished_builds`.`stage_id`')}"
  puts 'EXPECT: false (this was the double-evaluation root cause)'
  puts "stages scoped by traversal_path: #{sql.include?('`siphon_p_ci_stages`.`traversal_path`')}"
  puts 'EXPECT: true'

  section '2. Seed fixtures and verify stage_name still resolves correctly'
  pipeline = FactoryBot.create(:ci_pipeline, project: project, ref: 'master', source: 'push',
    started_at: base, finished_at: base + 30.seconds)
  stage = FactoryBot.create(:ci_stage, project: project, pipeline: pipeline, name: 'test')
  b1 = FactoryBot.create(:ci_build, :success, project: project, pipeline: pipeline, ci_stage: stage,
    name: 'rspec', started_at: base, finished_at: base + 3.seconds)
  b2 = FactoryBot.create(:ci_build, :failed, project: project, pipeline: pipeline, ci_stage: stage,
    name: 'rspec', started_at: base, finished_at: base + 5.seconds)
  path = project.project_namespace.traversal_path(with_organization: true)
  now = Time.current
  insert('siphon_p_ci_builds', [b1, b2].map do |b|
    { id: b.id, partition_id: b.partition_id || 100, project_id: project.id, commit_id: b.commit_id || pipeline.id,
      status: b.status, name: b.name, stage_id: b.stage_id, type: 'Ci::Build',
      started_at: b.started_at, finished_at: b.finished_at, created_at: b.created_at,
      updated_at: b.created_at, traversal_path: path, _siphon_replicated_at: now, _siphon_deleted: false }
  end)
  insert('siphon_p_ci_stages', [{ id: stage.id, partition_id: 100, project_id: project.id,
    pipeline_id: pipeline.id, name: stage.name, status: 0, position: stage.position,
    created_at: stage.created_at, updated_at: stage.created_at, traversal_path: path,
    _siphon_replicated_at: now, _siphon_deleted: false }])
  puts 'seeded 2 builds, 1 stage'

  query = finder.for_container(project).with_stages(project).select(:name, :stage_name).mean_duration.final_query
  rows = ClickHouse::Client.select(query, :main)
  rows.each { |r| puts "  name=#{r['name'].inspect} stage_name=#{r['stage_name'].inspect} mean_duration=#{r['mean_duration']}s" }
  puts 'EXPECT: rspec, stage_name=test, mean_duration=4.0s ((3+5)/2)'

  section '3. Stage from another project is not picked up (container scoping intact)'
  other_ns = FactoryBot.create(:namespace, path: "sjopt-o-#{uid}")
  other_project = FactoryBot.create(:project, namespace: other_ns, path: "sjopt-o-#{uid}")
  other_path = other_project.project_namespace.traversal_path(with_organization: true)
  insert('siphon_p_ci_stages', [{ id: stage.id, partition_id: 100, project_id: other_project.id,
    pipeline_id: pipeline.id, name: 'wrong-stage', status: 0, position: 1,
    created_at: stage.created_at, updated_at: stage.created_at, traversal_path: other_path,
    _siphon_replicated_at: now, _siphon_deleted: false }])
  rows = ClickHouse::Client.select(query, :main)
  rows.each { |r| puts "  name=#{r['name'].inspect} stage_name=#{r['stage_name'].inspect}" }
  puts "EXPECT: stage_name=test (not wrong-stage - other project's stage row with same id is excluded)"
ensure
  section 'Cleanup'
  if project
    [project, defined?(other_project) ? other_project : nil].compact.each do |p|
      tp = p.project_namespace.traversal_path(with_organization: true)
      %w[siphon_p_ci_builds siphon_p_ci_stages].each do |t|
        ClickHouse::Client.execute("ALTER TABLE #{t} DELETE WHERE traversal_path = #{quote(tp)} SETTINGS mutations_sync = 1", :main)
      end
      p.namespace.destroy!
    end
    puts 'cleaned up'
  end
end

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

Scope siphon stages join by container only