"Let us assume that we only store a handful of genders but millions of people"
Weird example because you will get assholes like me pointing out that you can store gender with a boolean.
Super fast aggregations in PostgreSQL 19
21–30 of 32 posts
Re: Super fast aggregations in PostgreSQL 19
#22Is this "super fast" as in "faster than previous Postgres" or as in comparable to duckdb etc?
DuckDB and other specialized DBs benefit from much more optimized math, data structures and data storage/ memory lookups I'd assume.
But a 5x increase simply by optimizing the planner is nothing to be ashamed of.
Re: Super fast aggregations in PostgreSQL 19
#23The example uses the syntax
FROM person AS p, gender AS j
WHERE p.gender_id = j.gender_id
FROM person as p
INNER JOIN gender as j
ON p.gender_id = j.gender_idRe: Super fast aggregations in PostgreSQL 19
#24Earlier quoted context omitted.
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…
I wonder if PG will ever implement plan caching like MSSQL so that the speed of the optimizer is less of a concern and it can take more time finding better plans rather than replanning on every execution of the same statement.
Postgres used to have plan caching inside the same session, and that was so disastrous that it was limited severely by default.
Plan caching is very much a two-edged sword; cache too aggressively, and the situation will be different between the runs. Cache too little, and your hit rates are useless.
Re: Super fast aggregations in PostgreSQL 19
#25The example uses the syntax FROM person AS p, gender AS j WHERE p.gender_id = j.gender_id Isn't it preferable to be explicit? Does some of the inefficiency come from lack of explicitness? FROM person as p INNER JOIN gender as j ON p.gender_id = j.gender_id
AFAIK these two joins are exactly the same once you get past the parsing. It's just a different way to write an inner join. It's translated into the same AST and so there's no difference in planning/execution.
Re: Super fast aggregations in PostgreSQL 19
#26Earlier quoted context omitted.
I wonder if PG will ever implement plan caching like MSSQL so that the speed of the optimizer is less of a concern and it can take more time finding better plans rather than replanning on every execution of the same statement.
Postgres used to have plan caching inside the same session, and that was so disastrous that it was limited severely by default. Plan caching is very much a two-edged sword; cache too aggressively, and the situation will be different between the runs. Cache too little, and your hit rates are useless.
Not sure how that makes sense, if the stats change significantly then caches would be evicted during the gathering of statistics.
I believe popular connection poolers and clients attempt to do plan caching through prepared statements and keeping the connection open.
My understanding its not easy to do in PG since connections are process based instead of thread based and the query plans are not serializable between processes, so they cannot be shared between connections.
MSSQL has been doing statement plan caching for at least 20 years and it did stored procedure plan caching before that.
Re: Super fast aggregations in PostgreSQL 19
#27Is this "super fast" as in "faster than previous Postgres" or as in comparable to duckdb etc?
toyed with pg_lake against our absolute dump of iceberg files (the nerds in my field call it a data lakehouse but engineering already has too many abstractions). it's pretty insane having postgres & the power of duckdb for mega aggregation, i threw a lot of wild windowed queries and aggregations at it and it seemed to really intuitively switch to using the duckdb jujutsu very well.
looking at migrating the rest of our catalog to iceberg now just to have the pg_lake option in our back pocket for future application development. it's so damn cool, as far as dbs go i haven't personally been involved in anything that needed more power than what postgres could deliver with writes. to be able to tack on bigboi analytics on top of it really consolidates a lot for us. im generally pretty cynical of these big saas peoples acquiring cool stuff but snowflake nabbing crunchydata here (crunchydata = guys who work on some pretty interesting postgres extensions) and helping them push this one to the a proverbial finish line and then open sourcing it was really great to see. i was worried when the acquisition went down because this was the major postgres thing i was really hoping someone would deliver, and crunchydata imo seemed to have the best plan outlined that understood the need.
Re: Super fast aggregations in PostgreSQL 19
#28"Let us assume that we only store a handful of genders but millions of people" Weird example because you will get assholes like me pointing out that you can store gender with a boolean.
The nicest word I would use to describe you is uninformed.
There are various standards (e.g. ISO/IEC 5218) to encode gender and they are never boolean.
Re: Super fast aggregations in PostgreSQL 19
#29The example uses the syntax FROM person AS p, gender AS j WHERE p.gender_id = j.gender_id Isn't it preferable to be explicit? Does some of the inefficiency come from lack of explicitness? FROM person as p INNER JOIN gender as j ON p.gender_id = j.gender_id
I personally prefer being explicit in this case. Seeing the word INNER triggers the right way to visualize the query in my head.
Re: Super fast aggregations in PostgreSQL 19
#30The example uses the syntax FROM person AS p, gender AS j WHERE p.gender_id = j.gender_id Isn't it preferable to be explicit? Does some of the inefficiency come from lack of explicitness? FROM person as p INNER JOIN gender as j ON p.gender_id = j.gender_id
AFAIK these two joins are exactly the same once you get past the parsing. It's just a different way to write an inner join. It's translated into the same AST and so there's no difference in planning/execution.
Perhaps in this very basic case they are exactly the same but is that still true if you add secondary WHERE conditions that apply to just one table, or if one "table" is actually a view with a complex query definition, or many other ways in which a very simple "example" can quickly get complicated?