Live data from Hacker News

Super fast aggregations in PostgreSQL 19

cybertec-postgresql.com

31–32 of 32 posts

Re: Super fast aggregations in PostgreSQL 19

#31

Earlier quoted context omitted.

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?

In general, you split up the WHERE condition at every top-level AND. Then you do either pullup or pushdown or both (it depends a bit on how your planner looks on the inside). In the end, you end up with the two cases being exactly the same before you start planning.

For outer joins (left/right/full), it's different, and there you absolutely need the explicit join syntax (for correctness). And semijoins are not expressed the same way at all, partially for weird historical reasons.

Re: Super fast aggregations in PostgreSQL 19

#32
post #27

Is 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 ou…

that's good to hear, I'm planning to try pg_lake soon

would love to see more docs about operationalising it

so far it looks like it may be possible to use the Crunchy PGO k8s and have the duckdb part as a sidecar

Post reply on HN