3 ms·

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

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.

refset 3y ago
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-subqueries https://pganalyze.com/blog/5mins-postgres-optimize-subquerie... [1] https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.html https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.h... [2] "Unnesting Arbitrary Queries" https://cs.emis.de/LNI/Proceedings/Proceedings241/383.pdf https://cs.emis.de/LNI/Proceedings/Proceedings241/383.pdf
- Sesse__ 3y ago
  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.
  - fuy 3y ago
    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.
dur-randir 3y ago
But PG is much more commonly used for OLTP, not OLAP. I'm baffled to this day that they enabled it by default.
- LunaSea 3y ago
  OLAP is a very common use case for PostgreSQL as well.

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.

refset 3y ago

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-subqueries https://pganalyze.com/blog/5mins-postgres-optimize-subquerie... [1] https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.html https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.h... [2] "Unnesting Arbitrary Queries" https://cs.emis.de/LNI/Proceedings/Proceedings241/383.pdf https://cs.emis.de/LNI/Proceedings/Proceedings241/383.pdf

Sesse__ 3y ago
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.
- fuy 3y ago
  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.

dur-randir 3y ago

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

LunaSea 3y ago
OLAP is a very common use case for PostgreSQL as well.