Interestingly, "aggregate first, join later" has been the standard way of joining fact tables in BI tools for a long time. Since fact tables are typically big and also share common dimensions, multi-fact joins for drill-across are best done by first aggregating on those common dimensions, then joining on them.

Makes you wonder how many cases there are out there of optimizations that feel almost second nature in one domain, but have never been applied to other domains because no one thought of it.

Running list of some of the changes/commits that have been done on the 19/dev branch (?):

* https://pgpedia.info/postgresql-versions/postgresql-19.html

Is this "super fast" as in "faster than previous Postgres" or as in comparable to duckdb etc?

The key idea here seems to be that if you’re grouping on a column on a related table you can do your main aggregation by grouping on the foreign key id on the primary table and use that as a proxy for the data on the related table that you’re actually grouping by.

In the examples given, it’s much faster, but is that mostly due to the missing indexes? I’d have thought that an optimal approach in the colour example would be to look at the product.color_id index, get the counts directly from there and you’re pretty much done.

I have a feeling that Postgres doesn’t make that optimisation (I’ve looked before, but it was older Postgres). And I guess depending on the aggregation maybe it’s not useful in the general case. Maybe in this new world it _can_ make that optimisation?

Anyway, as ever, pg just getting faster is always good.

Neat, I see how this can prevent a lot of frustration when it comes to making certain queries stay quick as complexity grows. I wonder if this means I can forget the trick to chose LATERAL queries all over the place for performance reasons.

[flagged]