Need to add clickbench to our full benchmarks (as an optional run)

[jdatcmd]

https://github.com/ClickHouse/ClickBench/

[ChronicallyJD]

Claiming this and starting now, so we do not repeat #413. I have the bench box free and
it has the headroom for the full 100M-row hits table, so I will take it end to end.

One design decision worth stating up front, because it shapes the deliverable rather than
being an implementation detail.

We should not vendor ClickBench's SQL into this repo. ClickBench is Apache-2.0, and
PROVENANCE.md draws the line at use, not at reading: we
read freely and copy nothing. Its 43 queries and its CREATE TABLE are the benchmark
definition, so writing our own versions would produce numbers that are not comparable to
anybody else's, which is the entire point of running it.

So the runner fetches the definition and the data from upstream at run time and our
tree carries only our own code. That also matches "optional run" in the title: nothing
downloads on a normal gate, and a full run is opt in.

What I am planning, unless you would rather it were shaped differently:

• bench/run_clickbench.sh, out of the matrix, opt in through an environment variable.
• A scale knob. The full table is 100M rows over roughly 70 columns; a 1M or 10M prefix
  gives a usable signal in minutes rather than hours, and the full run stays available.
• heap and columnar arms on one cluster, interleaved rather than swept, since the first
  arm in a sweep measures coldest ([[interleave-ab-perf-not-sequential]] is the lesson
  from Draft: cost-margin GUC for the FSST keep/drop decision (#155) #271).
• The result goes into docs/benchmarks.md with the shape and the caveats, not only the
  numbers. Every query where we lose gets recorded as loudly as the ones where we win.

I will report the load numbers and the on-disk sizes before the query timings, since those
are the two figures that decide whether the rest is worth reading.

[ChronicallyJD]

What ClickBench actually tells us to build, decomposed rather than guessed

The table in #424 says we lose on 11 of 43. That is not actionable on its own, so I took
the worst one apart. q24 is SELECT * FROM hits WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10, and it was 11.6x slower than heap.

Same table, same run, varying one thing at a time:

variation	heap	columnar
q24 as written	727 ms	8,099 ms	11.1x slower
same, but SELECT EventTime instead of SELECT *	711 ms	1,668 ms	2.35x slower
same LIKE, no top-N, count(*)	718 ms	1,565 ms	2.18x slower
count(*) WHERE CounterID = 62, an integer predicate	587 ms	21 ms	27.7x faster

1,728 rows of 11,110,833 survive the filter. That is 0.016 percent.

So the 8,099 ms is approximately:

• 6,431 ms, 79 percent, materialising the other 104 columns
• 1,544 ms evaluating LIKE over 11.1M text values
• 100 ms for the top-N
• 21 ms of scan

The last row of that table is the important context. The engine is not slow. The same
query shape with an integer predicate beats heap by 27.7x. Two specific things are slow,
they are separable, and one is four times larger than the other.

The two items, ranked by measured size

1. Late materialisation. This is #405, and I was wrong to propose closing it.

79 percent of the worst query is decoding columns that 0.016 percent of rows needed. The
scan filters, then materialises every projected column for every candidate row, when it
could carry positions and fetch the surviving ones.

I measured this on #405 with a fixed-width fixture, recovered 29 ms per 2M rows, and
proposed closing it as measured and declined. That recommendation was built on a
fixture that could not show the effect: fixed-width columns, no wide text, no selective
filter, no SELECT *. ClickBench supplies all four and the recoverable fraction is
6,431 ms of 8,099.

That is not a small correction to my earlier number. It is a different conclusion, and
#405 should not be closed. I will re-open the measurement there with this fixture.

It also explains q23, SELECT of five columns including two MIN(text) over a LIKE,
at 3.2x, and q22 at 1.8x. Same shape: filter hard, return few, materialise wide.

2. Text predicate evaluation, at roughly a quarter the size

1,544 ms to run one LIKE across 11.1M values against 21 ms for an integer equality.
LIKE '%x%' cannot use a zone map, and bloom filters are equality only, so every value is
decoded and matched. URL averages 90 bytes and reaches 4,068.

This is what q21 (2.2x), q28 (2.2x) and q29 (1.3x) are. Every text predicate in ClickBench
is a LIKE, a <> or a regex, and none of them can be pushed down today.

FSST is already in the write path. Matching a compressed needle against compressed strings,
without decoding, is the technique the FSST paper describes, and we would be applying it to
data we already store that way. Worth its own issue and its own measurement before anyone
commits to it.

3. Already filed

#423, the varlena batch-fold error, blocks turning the accelerations on at all.

4. Not a build item, a default

enable_group_vectorization and enable_ungrouped_vector_agg are off, and about 35 of the
43 queries are GROUP BY. Turning them on improves three queries and makes three worse,
q18 by 2.17x. So this is #369's question and not a switch to flip.

What I am doing next

Filing item 2 as its own issue, and re-opening the #405 measurement with the ClickBench
fixture rather than my old one. @jdatcmd, item 1 is squarely next to #369, so say if you
would rather hold it.

[ChronicallyJD]

Correcting a factual error I made on this issue: I said ClickBench is Apache-2.0. It is CC BY-NC-SA 4.0. ClickHouse/ClickHouse is Apache-2.0; ClickHouse/ClickBench is not, and I carried the license across without checking.

Full detail and what it changes is on #424. Short version: NonCommercial and ShareAlike are terms Apache-2.0 does not have, an in-tree copy would put NonCommercial material into an MIT distribution, and the runtime fetch this PR already uses turns out to be the design the real license calls for rather than the technicality I defended it as.