alt Hacker News> That's separate from query optimisation though, right? The query optimiser is presumably able to reason 'broadly' and not just at the level of individual expressions? High-level query-plan optimisation must be much more consequential than effective use of JIT compilation.
Yes, yes, and yes. For databases, query optimization (esp. join ordering for larger queries, which heavily depends on estimates) is fundamental. Query optimization happens at the level of the query plan, JIT compilation is only relevant afterwards. A bad query plan leads to asymptotically worse performance (e.g., bad join ordering with huge intermediate results).
On query plan execution: The "classical" model as used in e.g. Postgres is a pull-based iterator model, where operators implement a next() method yielding the next tuple and in there recursively call next() on their child operators (e.g., a next() of a select operator calls next() on its child operator, then applies the predicate [what Postgres JIT-compiles], and returns the tuple if the predicate was true). This can happen one tuple at a time (Postgres) or "vectorized" where multiple tuples are processed at once (e.g. DuckDB). A query-compiling database will split the tree into pipelines and compile each pipeline as one function (e.g., a pipeline will iterate over all the tuples from a source (e.g. tablescan) and a select operator then becomes an if statement inside that loop). This results in pretty tight loops, avoids per-tuple dispatch overhead, and enables more optimizations inside the JIT-ted code (e.g., tuple values don't need to be reloaded from memory all the time). (I find the original paper on query compilation [1] to be well readable.)