Workflow [PR], commit [0b43aeb]

Workflow [PR], commit [e3a48c0]

Summary: ❌
15 failures out of 80 shown:

Dear @amosbird, this PR hasn't been updated for a while. Will you continue working on it? If not, please close it. Otherwise, ignore this message.

@amosbird would it be possible to break this PR into few smaller ones?
If I understood everything correctly, this PR consists of couple of different optimizations.
I think it would make it easier to merge everything incrementally as it's easier to review and optimizations would not block each other for merge.
WDYT?

@antonio2368 This PR has already been split into several PRs (#82478 #81992 #82104 #82380 #82850 and more to come). This one is specifically for producing the official binary to run ClickBench.

ClickBench query Q4 SELECT COUNT(DISTINCT UserID) FROM hits; got faster by >4x (dashboard). I read through the list of included changes and I see some modifications in hash tables in the code. But it is not really clear which exact change caused the speedup.

Likewise,

• Q11 SELECT MobilePhone, MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM hits WHERE MobilePhoneModel <> '' GROUP BY MobilePhone, MobilePhoneModel ORDER BY u DESC LIMIT 10;
• Q23 `SELECT * FROM hits WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10;``
• Q27 SELECT CounterID, AVG(length(URL)) AS l, COUNT(*) AS c FROM hits WHERE URL <> '' GROUP BY CounterID HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
• Q30 SELECT SearchEngineID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits WHERE SearchPhrase <> '' GROUP BY SearchEngineID, ClientIP ORDER BY c DESC LIMIT 10;

got impressive speedups.

@amosbird Do you have an idea what changes speed these up? Asking so we can prioritize the corresponding PRs.

Do you have an idea what changes speed these up?

Yes, I do.

• Q4 is faster because count_distinct_optimization is enabled by default in this PR. This is the easiest optimization to achieve, and since similar techniques are used in DuckDB and Apache Doris, it makes sense to enable it by default.

• Q23 is sped up by dynamic pushdown of the TopN predicate. This part isn’t yet separated in the PR. It can be prioritized, but I’m less focused on it than the next improvement.

• All other aggregation queries benefit from a new hash table implementation. I plan to use this to replace my old string hash table implementation. This part isn’t separated in the PR yet either, but I’ll likely work on it this week (I have several pending PRs at the moment...).

As a personal exercise in writing and building ClickHouse code, I cherry-picked Push TopN threshold to MergeTreeSource
and built it -- it's quite fast! I made an example table:

CREATE TABLE test
ENGINE = MergeTree
ORDER BY (id, time)
AS SELECT
    CAST(number, 'UInt8') AS id,
    CAST(number, 'UInt64') AS time
FROM numbers(10000)

and the simple query SELECT * FROM test ORDER BY time LIMIT 10 goes from ~2s to 0.8s with the optimization! However, when I add SETTINGS max_threads=1, the speedup disappears, and doesn't come back even if I add a min-max index on time, so I have a few curiosities:

1. What exactly is the change that the TopN optimization is making to the pipeline? From the code it looks like it's adding an initial prewhere node that filters based on a global threshold state.
2. Why does the speedup disappear with one thread? Since the table is partially ordered by time, I would expect the global threshold to very quickly reach 9 and thus filter out all greater values (most of the table). Is my mental model wrong?
3. Now that skip indexes can be used on data reading, will it be possible to apply skip indexes based on the global threshold? Is that compatible with the current shape of the change?
4. I initially wanted to extract the TopN optimization into its own PR, both because I am interested in the feature, and to learn how to contribute to ClickHouse. Does this sound fine to you? Maybe it's too difficult a first change to try and introduce / rebase onto current master? What are your thoughts here?

Dear @amosbird, this PR hasn't been updated for a while. Will you continue working on it? If not, please close it. Otherwise, ignore this message.