Turning a query engine benchmark into a performance regression test

A benchmark answers “how fast is it?”. A regression test answers a narrower and more useful question: “did this change make it slower, by an amount that matters, with evidence I can defend?”. In Part 1 and Part 2 of this series I set up a workload derived from TPC-H on Trino and captured per-query statistics. Part 3 covered reading plans to explain a slow query. This part is about making the benchmark detect regressions on its own.

I have marked this piece as working rather than settled. The statistics are standard; the thresholds and run counts are judgement calls that depend on your environment, and I expect to refine them.

What counts as a regression

I use two conditions, and both must hold:

1. Material. The change is larger than a threshold that someone would care about. For a query that takes 15 seconds, a 2% change rarely matters. For one that takes 300 milliseconds, even 30% may be below anyone’s notice.
2. Detectable. The change is larger than the noise. The candidate’s distribution of run times is shifted relative to the baseline’s, not just its single fastest or slowest run.

Material without detectable is a guess. Detectable without material is noise in the issue tracker. A gate that checks only one of the two will either miss real problems or train people to ignore it.

Where the noise comes from

Before choosing thresholds, measure the noise. On a Trino cluster reading Iceberg tables from object storage, the usual sources are:

• JIT and cache warm-up. The first runs after a restart are slower. Discard warm-up iterations.
• Object storage latency. S3-compatible storage adds variance that the engine does not control.
• Garbage collection and memory pressure. Large aggregations (Q18 is the classic) can trigger pauses that make one run in several slow.
• Neighbours. Other workloads on the same nodes, network or bucket.
• Drift during the run. Background compaction, autoscaling or a cache filling over time.

The most effective tool I know for this is the A/A test: run the same build against itself many times and look at the spread per query. That spread becomes the basis for every threshold below.

Two observations generalise well. Short queries are noisy in relative terms because fixed costs such as planning and scheduling dominate. Memory-heavy queries are noisy because of garbage collection and contention. A single suite-wide threshold will be too loose for some queries and too tight for others.

CPU time is usually quieter than wall time, because it mostly reflects the work the plan does rather than waiting. I gate on wall time, because that is what users feel, but I show CPU time next to it. When both move, the plan probably changed. When only wall time moves, look at the environment first.

How many runs

The number of runs is not a matter of taste. It limits what a test can ever detect.

Take the Mann-Whitney U test, a rank-based test that suits skewed timing data. With three runs on each side, there are only 20 ways to arrange the ranks, and the smallest two-sided p-value the exact test can produce is 0.1. No result, however extreme, can be significant at 0.05. With four runs per side the floor is about 0.029, and with five it is about 0.008.

So I use at least five measured runs per side for the CI gate and seven or more where queries are noisy, after warm-up runs that are discarded. More runs narrow the confidence interval, but the cost is linear in suite time, which is why the CI tier runs at a small scale factor.

Baseline strategy

Comparing a candidate with a baseline recorded weeks ago on a cluster that has since been patched compares two environments, not two builds. I prefer, in order:

1. Interleaved A/B. Run baseline and candidate on the same cluster in the same session, alternating (A, B, A, B) per query. Drift affects both equally. This is the most reliable and the most expensive.
2. Fresh baseline. Re-measure the baseline build on the same environment at the start of the job.
3. Stored baseline. Compare with a recorded run from the same environment manifest. Cheapest, and acceptable for nightly trend tracking, provided the manifests match.

Baselines are per query, per scale factor and per environment. They are updated deliberately, through a reviewed change, never automatically on every green build. Automatic updates let a series of small regressions each pass against the one before.

The statistical comparison

For each query I compute three things from the two samples:

• The ratio of medians, candidate over baseline. Medians resist the occasional slow run.
• A bootstrap confidence interval for that ratio, resampling each side independently. This says how sure we are about the size of the change.
• A Mann-Whitney U p-value, which asks whether one sample tends to be slower than the other.

The verdict combines them with a per-query threshold and an absolute floor.

import numpy as np
from scipy.stats import mannwhitneyu

def compare(baseline, candidate, threshold=0.10, alpha=0.01,
            floor_ms=200, n_boot=10_000, seed=7):
    """Classify one query's change from repeated wall-time samples in ms."""
    b, c = np.asarray(baseline, float), np.asarray(candidate, float)
    ratio = np.median(c) / np.median(b)

    # Bootstrap the ratio of medians, resampling each side independently.
    rng = np.random.default_rng(seed)
    bs = rng.choice(b, (n_boot, b.size))
    cs = rng.choice(c, (n_boot, c.size))
    boot = np.median(cs, axis=1) / np.median(bs, axis=1)
    lo, hi = np.percentile(boot, [2.5, 97.5])

    p = mannwhitneyu(c, b, alternative="two-sided").pvalue
    delta_ms = np.median(c) - np.median(b)

    if abs(delta_ms) < floor_ms:
        verdict = "pass"              # too small to matter in absolute terms
    elif ratio > 1 + threshold and lo > 1 and p < alpha:
        verdict = "regression"
    elif ratio < 1 - threshold and hi < 1 and p < alpha:
        verdict = "improvement"
    elif abs(ratio - 1) > threshold:
        verdict = "inconclusive"      # large effect, weak evidence: rerun
    else:
        verdict = "pass"
    return {"ratio": float(ratio), "ci": (float(lo), float(hi)),
            "p": float(p), "delta_ms": float(delta_ms), "verdict": verdict}

# Illustrative samples, not measured results (Q18, seven runs each)
base = [14820, 14950, 15010, 14700, 15230, 14890, 15100]
cand = [16900, 17150, 16820, 17400, 16990, 17080, 17300]
print(compare(base, cand))
# ratio about 1.14, CI about (1.12, 1.16), p under 0.001 -> regression

A few design decisions are worth stating:

• Inconclusive is a real outcome. A large change with weak evidence triggers an automatic rerun with more iterations, not a pass and not a fail.
• Improvements are tested too. A query that becomes much faster is as suspicious as one that becomes slower until its result checksum confirms the answer is unchanged.
• Twenty-two queries means twenty-two tests. At an alpha of 0.05 per query you should expect about one false alarm per run in the long run. I use a stricter alpha (0.01 in the example) and require a regression to reproduce on rerun before the gate fails. A formal correction such as Holm’s method is an alternative.

Thresholds per query

Thresholds are example values that you calibrate, not constants. The rule I start with:

Then validate the rule. Run A/A comparisons and count how often the gate fires; that is your false-positive rate. Inject known regressions, for example forcing join_distribution_type to PARTITIONED or turning off enable_dynamic_filtering for a session, and confirm the gate catches them on the queries where Part 3 says it should.

Wiring it into CI

The pull request tier runs at SF10 on dedicated runners, with interleaved baseline and candidate runs, and posts a per-query table as a comment. It fails only on confirmed regressions. The nightly tier runs at SF100 against a stored baseline, catches memory-bound regressions that the small scale hides, and opens an issue with the plan diff attached.

Dedicated runners matter more than any statistic. If the CI job shares nodes with other workloads, the A/A noise grows until no threshold is useful.

Reporting regressions responsibly

A regression report should let a reviewer agree or disagree without rerunning anything. Mine contains:

• The query, the ratio of medians, the confidence interval, the p-value and the run counts.
• The environment manifests for both sides, and confirmation they differ only in the intended change.
• EXPLAIN ANALYZE output for both sides, diffed, as described in Part 3.
• CPU time, peak memory and spilled bytes alongside wall time.

The full harness this plugs into, including the runner, manifest and result store, is described in the benchmark harness case study.

Further reading

• SciPy mannwhitneyu and SciPy bootstrap
• TPC fair use quick reference
• Reproducible benchmark harness for Trino, derived from TPC-H
• Part 3: Reading Trino query plans to find bottlenecks in TPC-H derived queries
• Part 2: Measuring Performance Excellence, Trino TPC-H Benchmark