1-- The latest run against the last release's recorded runs, with a2-- threshold that allows for noise. Run by scripts/stress/report.sh after3-- load.sql, with variables threshold, min_delta_ns and release_tags, a4-- list of 'tag commit' strings, newest release first (from git), or a5-- single run id in release_run.6--7-- The release's runs are every clean run of the newest tagged commit that8-- has one on the latest run's CPU and OS, other than the latest run9-- itself; its samples are pooled. A benchmark regresses when its median10-- is slower than the release's by more than threshold + noise and by more11-- than min_delta_ns, where noise is the larger spread,12-- (max - min) / median, of its samples in either side.1314CREATE OR REPLACE TEMP TABLE release_tags AS15SELECT split_part(t, ' ', 1) AS tag, split_part(t, ' ', 2) AS commit, i AS ord16FROM (SELECT unnest(getvariable('release_tags')) AS t, generate_subscripts(getvariable('release_tags'), 1) AS i)17WHERE t <> '';1819CREATE OR REPLACE TEMP TABLE release_pick AS20WITH latest AS (SELECT * FROM runs WHERE age = 1),21cand AS (22 SELECT t.tag, t.ord, r.run_id23 FROM release_tags t24 JOIN runs r ON starts_with(t.commit, r.git_sha) AND length(r.git_sha) >= 725 JOIN latest l ON r.cpu = l.cpu AND r.goos = l.goos26 WHERE NOT r.dirty AND r.run_id <> l.run_id AND r.git_sha <> l.git_sha27),28tag AS (29 SELECT tag FROM cand ORDER BY ord LIMIT 130)31SELECT run_id, tag FROM cand WHERE getvariable('release_run') = '' AND tag = (SELECT tag FROM tag)32UNION ALL33SELECT run_id, run_id AS tag FROM runs WHERE run_id = getvariable('release_run');3435CREATE OR REPLACE TEMP TABLE release_compared AS36WITH l AS (SELECT r.* FROM bench r JOIN runs u USING (run_id) WHERE u.age = 1),37rel AS (38 SELECT r.pkg, r.name, count(*) AS samples, median(r.ns_op) AS ns_op,39 CASE WHEN median(r.ns_op) > 0 THEN (max(r.ns_op) - min(r.ns_op)) / median(r.ns_op) ELSE 0 END AS spread40 FROM results r JOIN release_pick p USING (run_id)41 GROUP BY r.pkg, r.name42)43SELECT l.pkg, l.name, rel.ns_op AS before_ns, l.ns_op AS after_ns,44 (l.ns_op - rel.ns_op) / rel.ns_op AS change,45 greatest(l.spread, rel.spread) AS noise,46 rel.samples AS release_samples, l.samples AS latest_samples,47 CASE48 WHEN (l.ns_op - rel.ns_op) / rel.ns_op > getvariable('threshold') + greatest(l.spread, rel.spread)49 AND l.ns_op - rel.ns_op > getvariable('min_delta_ns') THEN 'REGRESSION'50 WHEN (rel.ns_op - l.ns_op) / rel.ns_op > getvariable('threshold') + greatest(l.spread, rel.spread)51 AND rel.ns_op - l.ns_op > getvariable('min_delta_ns') THEN 'faster'52 ELSE ''53 END AS flag54FROM l JOIN rel USING (pkg, name);5556.print57.print # Latest against the last release (threshold plus each benchmark's noise)58SELECT (SELECT any_value(tag) FROM release_pick) AS release,59 (SELECT count(*) FROM release_pick) AS release_runs,60 count(*) AS compared,61 count(*) FILTER (WHERE flag = 'REGRESSION') AS regressions,62 count(*) FILTER (WHERE flag = 'faster') AS faster,63 printf('%.0f%%', getvariable('threshold') * 100) AS threshold64FROM release_compared;6566SELECT pkg, name, fmt_ns(before_ns) AS release, fmt_ns(after_ns) AS latest,67 printf('%+.1f%%', change * 100) AS change,68 printf('%.1f%%', noise * 100) AS noise,69 release_samples || '/' || latest_samples AS samples, flag70FROM release_compared WHERE flag <> ''71ORDER BY flag DESC, change DESC;