Can GROUP BY still be optimized after all these years?
pivotlake.io96 points by ntur1337 4 days ago
96 points by ntur1337 4 days ago
I wonder why you didn't opt to perform a Top-K aggregation in each partition in parallel. That would give you both a reduction of elements in each partition and also the correct maximum of each partition, which can be used for further pruning.
Their clickbench results look really good.
https://benchmark.clickhouse.com/#system=+liH|Hoaus|DcD|ucqs...
Neat! For others, the data structure they are describing is a count-min sketch. It looks like they're using it to set a bound on elements that can enter the group-by and using it to skip a bunch of extra work
I wonder if with a LLM you could better analyse and make a strategy for querying. Maybe even live restructuring of data models based on its usage.
22.2ms vs 35.6ms is 1.6x faster! It's 0.4x faster and even doing worst math possible it's 0.62x faster.
Can't we really keep the IT without breaking news/we are all gonna die/sensationalist way of passing news?
It's 60% faster (0.6x faster, 1.6x as fast). Throughput (“how much stuff you can do per unit of time”) is the only reasonable way to describe performance[1], otherwise large speedups are basically impossible to describe in an intuitive way. (If something that took 10s now takes 1s, that's “ten times as fast”, not “zero point nine times faster”.)
[1] Even latency changes are best described in terms of throughput.
The problem is that “faster” and “slower” compare speed, which is x units per y time. But here, we’re only comparing time. The semi-legit interpretation is “if one unit takes 22.2ms, how many units can I complete in 35.6ms”, and actually the answer to that is indeed the 1.6 number they mention.
It’s not sensationalist, it’s just a bit sloppy with the word “faster”.
More importantly, WHY is mysql GROUP BY still broken as default? MySQL is the PHP of databases, but it seems they have no plan to fix bugs.
MySQL GROUP BY is not broken by default (ONLY_FULL_GROUP_BY, the standards-conforming choice, is default for a decade or more). But there is basically no team left on the MySQL optimizer, after a long series of layoffs; it's gone from something like 40 to 3, give or take. Try Postgres :-)
You know, I have a hard time seeing how it can be cheaper to run a dataset across a wire rather than just send its grouped results. That's really the problem. I mean, who cares if it takes 100ns to group by on a box when it will take 10 times much for the data to get to it and back.
The fastest possible group by is in sequential memory on that box, fetched from an nvme.
So, when we do group by partitioning, we're really saying, put the groups themselves on different boxes, and group the group by. If it were some utter megaset, the place to put it is on a mainframe, which is basically created for purposes of group by on ridiculous datasets.
At least they have useful technical information in it. Personally really like reading these blog posts.
Measurements and context is so situational that it would be useless anyway imo.
It is not like any DB company is writing blog posts with benchmarks compared to hardware limits. DuckDB had blog posts that has sentences like "You are doing it wrong if you are not using HTTP api on your database in 2026", last time I saw their blog.
I also think object storage is bad in this case. And rust is no a no-no for a database project like this, because it is not ergonomic to do static allocation and arena allocators in rust. And parquet is trash for performance because of many reasons like supported compression formats etc.
> And rust is no a no-no for a database project like this, because it is not ergonomic to do static allocation and arena allocators in rust.
I'm not sure ergonomics matter that much these days, when your agent can write the tedious bits?
You're not wrong: if you know how much memory you need for a single query and can keep the data in attached SSD (so ephemeral SSD on AWS), scaling vertically will give you the fastest execution time.
However horizontal scaling has advantages in that it's easier to scale up and down incrementally. It gives you more aggregate network bandwidth for accessing disaggregated data. Disaggregation allows for independent scaling of compute and storage. Losing a single machine has a marginal impact on the cluster, which can enable you to use cheaper spot resources.
Basically, it's often easier to operate a system designed around horizontal scaling if you need resiliency.
[dead]