Stream rebuilt projection blocks through one bounded run merger into the final

perfloop/clickhouse · INEFFICIENT ALGORITHM

https://perfloop.ai/t/oss/case_cwwjz67qcr

Verdict

VERIFIED · settled 2026-09-06 · pull request opened as ClickHouse/ClickHouse#118451

What happened: The paired measurements met the required improvement.

Hypothesis

The worker-driven coroutine reaches `MergeTask::ExecuteAndFinalizeHorizontalPart::executeImpl`, where every pulled merged block is written to the parent part and also enters `calculateProjections` whenever `prepareProjectionsToMergeAndRebuild` has selected a rebuild—for example because the merge can reduce rows, source columns expired, or merge materialization is enabled. The source shows that each resulting squashed projection batch is sorted, written as a full temporary projection part, finalized, and committed; after the horizontal stream ends, `executeMergeProjections` creates `MergeProjectionPartsTask`, which takes at most ten parts at a time, recursively calls `mergePartsToTemporaryPart` through `executeHere`, and commits intermediate outputs. Thus a rebuilt projection can pay repeated full-part serialization, metadata/transaction work, decode, merge, and recompression over a fan-in tree even though all of its rows originated in the same parent merge. A stateful bounded run merger changes that whole path so its unavoidable floor is projection evaluation plus the ordered/aggregated final projection write, with spill only when the projection’s ordering or aggregate state requires it, rather than a committed MergeTree part and nested merge task for every batch. A later traced merge benchmark that forces both normal and aggregate projection rebuilds across more than the current ten-part fan-in should compare end-to-end merge time, CPU, projection bytes read and written, temporary-part commits, and peak memory while verifying identical projection contents and cancellation behavior; lack of material remerge I/O/CPU or failure to preserve the bounded-memory and correctness checks would falsify the case.

Change to test: Replace the rebuild branch’s per-squashed-block temporary projection parts with persistent per-projection rebuild sinks fed from the horizontal merge output. Each sink should retain projection evaluation plus ordering/aggregate state across `executeImpl` pulls, append order-compatible output directly to its final projection writer, and otherwise spill bounded projection runs followed by one bounded external k-way merge/aggregate directly into that final writer. Finalize and attach those projection parts from the outer merge task, retaining the existing source-projection merge path for `projections_to_merge`, but remove the per-batch `writeTempProjectionPart`/commit path and the `MergeProjectionPartsTask` recursive child-`MergeTask` tree for rebuilt projections.

Where it lives

perfloop/clickhouse · src/Storages/MergeTree/MergeTreeBackgroundExecutor.h

Evidence

fixed 19-mode rebuilt-projection merge matrix across ordinary, reducing, vertical, TTL, filtering, index, dynamic, and multi-projection cases · 10 sample pairs

metric baseline candidate paired median change confidence range required result
rebuilt_projection_matrix_merge_seconds 121 99.06 −17.8% (−21.5) −23.28 to −20.41 < −6.051 PASSED
rebuilt_projection_matrix_cpu_seconds 91.34 72.46 −21.1% (−19.25) −19.84 to −17.63 < −4.567 PASSED
rebuilt_projection_matrix_projection_serialization_compressed_bytes 1524563570 1193832228 −21.7% (−330731342) −330731342 to −330731342 < −76228179 PASSED
rebuilt_projection_matrix_temporary_projection_part_commits 997 0 −100% (−997) −997 to −997 < −49.85 PASSED
rebuilt_projection_matrix_merge_file_read_compressed_bytes 4849962771 4516000898 −6.9% (−333888472) −334157625 to −333711709 ≤ 0 PASSED
rebuilt_projection_matrix_merge_file_write_compressed_bytes 3056994562 2719017655 −11.1% (−338024859) −338409424 to −337479441 ≤ 0 PASSED
rebuilt_projection_matrix_peak_rss_kib 2032208 2036274 +0.1% (+1808) −64808 to +42212 ≤ 65536 PASSED

Checks: 3 of 3 passed. Verification: no defect found.

Timeline