11–20 of 32 posts
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 wou…
You're saying “the missing indexes” as if you could add indexes for every join you're ever doing and that this would be faster than a hash join. For many systems, that's not feasible nor very performant; and depending on selectivity, hash join would often be better than an index lookup anyway.
The biggest win from early aggregation is that you can reduce the number of rows significantly before you go join in other things (which would be a win even in nested-loop index lookup joins; smaller joins are nearly always better along every axis).
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 wou…
1. Index-only scans on t_product.{category,color} indices, summing each value
2. Lookup the names of those values in their parent tables, generate output rows
If so, I suspect there are two reasons why it might not do that:
Given the relatively small size of the t_product table (23 bytes overhead + 1 byte padding + int4 + int4 + 16 bytes text + [I think] 1 byte varlena = 49 bytes/row), it will be fairly well bin-packed into pages on the heap, consuming roughly 170 pages, assuming 8 KiB default, and default fillfactor of 100%). That trivially fits into a single segment file on-disk, and is a very easy sequential scan.
If it does a sequential scan on the heap, it doesn’t have to check the Visibility Map, because it already has that information in the heap itself, which avoids a second (albeit small) lookup.
Happy for someone who knows more about Postgres to correct me if I’m wrong, though!
* 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?
e.g. the gender_name example would already be optimized in duckdb via columnar execution and “aggregate first, join later” planning.
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 d…
Probably quite a lot, being a specialist in multiple domains is getting more difficult.
I believe that's one of the reasons why it took about ~8 years (the original patch was proposed in 2017).
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 wou…
As for indexes, it can help, but not in this particular example - the "code" tables are tiny, and the planner adds Memoize nodes anyway, so it acts like an ad hoc index.
Indexes are more of a complementary improvement, not an alternative to this optimization (i.e. neither makes the other unnecessary). FWIW in this case the indexes won't help very much - if you use more data in the code tables, it'll use a hash join, not nested loop / merge join.
That doesn't mean we couldn't do better with indexes, there probably are smart execution strategies for certain types of queries. But indexes also come with quite a bit of overhead (even in read-only workloads).
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 d…
I don't think it's that nobody thought of it for PostgreSQL - I think it's that making sure it worked completely reliably across the entire scope of existing PostgreSQL features to their level of required quality took a bunch of effort.
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 d…
It's not that nobody thought of it. Group pushdown has been a thing in papers for ~10 years at least, but it's hard to plan; your search space (which was already large) explodes, and it's always hard to know exactly how many rows come out of a given grouping. I have no idea how Postgres deals with these. Hopefully, they're doing something good (enough) :-) Next up would hopefully be groupjoin, where you combine group…