mirror of
https://github.com/hanzoai/datastore.git
synced 2026-09-26 13:55:07 +00:00
Raúl MarínandClaude Opus 4.8
d9c2bf2010
eqmed.sql runs a randomization test for the median difference between the
two versions. It cross-joined each query's measurements with
numbers(1, 10000) and did `order by rand()`, so a query with 1000
measurements built and sorted 10M rows in memory: ~22 GiB peak and ~15s,
single-threaded. That 22 GiB spike is why the analyze stage caps
concurrency at MemTotal/15.
Two changes, same statistic (10000 iterations, exact medians):
- Keep each query's measurements as one array and reshuffle it per
iteration (arrayShuffle + arrayReduce('medianExactForEach', ...) on each
half), with max_block_size bounding how many shuffled copies exist at
once, instead of materializing and sorting numbers*T rows.
- Only randomize metrics that actually vary across the measurements. A
constant metric has identical medians in every split, so its threshold
is exactly 0 (which the old query also produced); most of the ~230
ProfileEvents are constant per query. The threshold is scattered back to
full width with 0 for the constant metrics.
Verified on 505 real query inputs from a CI run: the deterministic
columns (left/right medians, diff) are byte-identical, and the randomized
threshold is statistically identical (median ratio 1.0; the only
differences are Monte-Carlo boundary flips the old query shows run to run
as well). On the heaviest query (1000 measurements) peak memory drops
from 22.5 GiB to 788 MiB and time from 15.4s to 2.48s; total CPU over all
505 queries drops from 896s to 225s.
Co-Authored-By: Claude Opus 4.8 (1M context) <[email protected]>
63 lines
2.8 KiB
SQL
63 lines
2.8 KiB
SQL
-- The input is table(test text, query text, run UInt32, version UInt8, metrics Array(float)).
|
|
-- Randomization test for the median difference between the two versions.
|
|
--
|
|
-- Two optimizations over the naive cross-join approach:
|
|
-- 1) Keep each query's measurements as one array and reshuffle per iteration
|
|
-- (bounded by max_block_size), instead of materializing/sorting numbers*T rows.
|
|
-- 2) Only randomize metrics that actually vary across the measurements.
|
|
-- A metric that is constant has identical medians in every random split, so
|
|
-- its threshold is exactly 0; computing it is pure waste (most of the ~230
|
|
-- ProfileEvents are constant per query). The threshold is scattered back to
|
|
-- full width with 0 for the constant metrics.
|
|
WITH
|
|
(SELECT groupArray(metrics) FROM table) AS all_metrics,
|
|
length(all_metrics[1]) AS num_metrics,
|
|
arrayFilter(i -> arrayMin(arrayMap(row -> row[i], all_metrics))
|
|
!= arrayMax(arrayMap(row -> row[i], all_metrics)),
|
|
range(1, num_metrics + 1)) AS varying,
|
|
arrayMap(row -> arrayMap(i -> row[i], varying), all_metrics) AS projected
|
|
SELECT
|
|
arrayMap(x -> floor(x, 4), original_medians_array.medians_by_version[1] as l) l_rounded,
|
|
arrayMap(x -> floor(x, 4), original_medians_array.medians_by_version[2] as r) r_rounded,
|
|
arrayMap(x, y -> floor((y - x) / x, 3), l, r) diff_percent,
|
|
arrayMap(x, y -> floor(x / y, 3), threshold, l) threshold_percent,
|
|
test, query
|
|
from
|
|
(
|
|
-- randomization over the varying metrics only, then scattered back to
|
|
-- full width (constant metrics get threshold 0)
|
|
select arrayMap(i -> if(has(varying, i), thr_small[indexOf(varying, i)], 0.),
|
|
range(1, num_metrics + 1)) threshold
|
|
from
|
|
(
|
|
select quantileExactForEach(0.99)(d) thr_small
|
|
from
|
|
(
|
|
select
|
|
arrayShuffle(projected) as sh,
|
|
intDiv(length(sh), 2) as h,
|
|
arrayReduce('medianExactForEach', arraySlice(sh, 1, h)) as ma,
|
|
arrayReduce('medianExactForEach', arraySlice(sh, h + 1)) as mb,
|
|
arrayMap(x, y -> abs(x - y), ma, mb) as d
|
|
from numbers(10000)
|
|
settings max_block_size = 64
|
|
)
|
|
)
|
|
) rd,
|
|
(
|
|
select groupArrayInsertAt(median_metrics, version) medians_by_version
|
|
from
|
|
(
|
|
select medianExactForEach(metrics) median_metrics, version
|
|
from table
|
|
group by version
|
|
) original_medians
|
|
) original_medians_array,
|
|
(
|
|
select any(test) test, any(query) query from table
|
|
) any_query,
|
|
(
|
|
select throwIf(uniq((test, query)) != 1) from table
|
|
) check_single_query
|
|
;
|