Benchmarking Analytics Queries Honestly

Why most benchmarks are worthless

A benchmark showing one tool 100× faster than another is usually comparing a warm cache to a cold one, or a well-indexed query to an unindexed one, or uniform synthetic data to something resembling reality.

The result is technically true and practically meaningless. If you are going to publish numbers, publish ones that survive scrutiny.

Generate realistic data

Real analytics data is not uniform:

• Traffic is skewed. A handful of pages take most views; a long tail takes one each.
• Time is not flat. Weekday peaks, weekend troughs, daily cycles.
• Cardinality varies wildly. Two dozen countries, thousands of paths, millions of visitor ids.

Uniform random data makes every engine look good, because it makes every index look good. Zipf-distributed paths and a realistic time curve produce the query plans you will actually hit.

Separate cold and warm

Report both, labelled:

• Cold — first execution after a restart. Cache empty, files unread.
• Warm — repeat execution. What a busy dashboard actually experiences.

Cold is the honest worst case. Warm is the common case. Quoting only one is where most published comparisons go wrong.

Percentiles, not averages

An average hides the experience that annoys people. Report p50, p95 and p99.

A query averaging 40 ms with a p99 of 4 seconds is a bad query that looks fine. One in a hundred dashboard loads feels broken, and the average never shows it.

Run enough iterations

A single timing measures scheduler noise. Twenty or more iterations, discard the first few as warm-up, report the distribution.

If the spread across runs is wider than the difference you are claiming, you have not measured a difference.

Publish the harness

The most useful thing a benchmark can include is the code that produced it. Seed script, queries, hardware, versions, methodology.

Without that, a reader has to take the numbers on faith — and given how often benchmarks are wrong, they should not.

Benchmark what people do

The queries worth measuring are the ones your product actually runs: the dashboard's KPI summary, the top-pages aggregate, a funnel, a 90-day traffic series.

A synthetic SELECT COUNT(*) measures nothing anyone experiences. If a query is not on a page someone opens, its timing is trivia.