Anyone got a succinct explanation for why ClickHouse is doing so well in this kind of benchmark? What are the key tricks they're using to get great analytical performance, and why haven't those same tricks been adapted by other competing systems?
Imagine you are implementing COUNT(DISTINCT) function. And imagine it is used in GROUP BY something, like GROUP BY region_id. And on realistic datasets, you will have a large number of different region_id and something like power law distribution of their frequency: https://en.wikipedia.org/wiki/Zipf%27s_law
Then you will expect that most of the values of COUNT(DISTINCT) will be small. And the natural optimization will be - using a linear array before using a hash table. Or: place smaller hash tables in something like a memory pool.
This is about a ton of optimizations on distributions of real datasets.