Hacker News Reader

Comment by LunaSea | Hacker News Reader

The JIT compiler is great for analytical queries.

You can configure thresholds for the JIT activation in PostgreSQL as well if you want to elevate the bar from which the JIT is enabled.

View on HN

For analytical queries note that you really have to learn how to express the queries efficiently with Postgres - unfortunately the optimizer is still missing lots of tricks found in more sophisticated engines [0][1][2]. JIT compilation alone can't get close to making up for those gaps.

[0] https://pganalyze.com/blog/5mins-postgres-optimize-subquerie...

[1] https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.h...

[2] "Unnesting Arbitrary Queries" https://cs.emis.de/LNI/Proceedings/Proceedings241/383.pdf

Postgres isn't really hot on implementing the latest stuff from academia (most of what they do seem to be fairly home-grown, not the least because some of it was never really written about before they started doing it). Arbitrary unnesting is certainly one of the optimizations they don't do; fancy sketches and other estimation techniques generally likewise. But what they're really good at is polish. A thousand small tweaks and optimizations to iron out issues for 0.1% of queries. It tends to have fairly few sharp edges, less so than any other RDBMS I've used.

Thomas Neumann and Alfons Kemper papers can hardly be called "home-grown", their stuff has been implemented in multiple industrial systems. Postgres optimizer, though, is very bad even with simple correlated subqueries, let alone "arbitrary", so it'd be useful to have at least some version of unnesting.

But PG is much more commonly used for OLTP, not OLAP. I'm baffled to this day that they enabled it by default.

OLAP is a very common use case for PostgreSQL as well.

Reply on news.ycombinator.com