Thanks (author here ;)). Note it's currently not the actual queries themselves that get JIT compiled (started to work on that for v12), but evaluation of expressions inside queries. WHERE clauses, aggregates, projections (SELECT ... lists), grouping conditions are now all handled through expression evaluation and therefore can be JITed. The control flow between different type of executor nodes not yet however.
Additionally tuple deforming (i.e. converting the on-disk representation into a more easy to use / faster to access in-memory representation) is also JITed. But currently only when done from within an expression, but that's mostly the case.
> especially for linear scans of a large number of rows, which I imagine is where you would see the most improvement.
It's basically a benefit whenever expressions get invoked on a large number of rows. That can be aggregates over a large sequential scan, but it can be beneficial for a good chunk of other types of queries too.
> I wonder if there are any other databases that do this, and what the advantages/disadvantages are? I'm fairly certain AWS Redshift does this also.
Yes, there's several other databases that do this too. The disadvantages are largely that it adds processing overhead / increases latency till the query can be actually processed. Which means that if the, currently pretty simplistic, logic to guess whether it's worth to JIT is wrong, you make the query slower due to the added effort to JIT the expressions.
You can hide a lot of that by doing JIT compilation in parallel and doing smart caching, but that's currently not done yet.
We could, but currently don't yet. Not enough time / too short days...
That's not true for all that many analytics statements actually. Good storage systems can deliver data quite fast, and in many cases the buffer cache can hide a lot of the access latency too.
There's obviously issues with memory access latencies, but that's actually something that JIT compilation can hide with, because it increases the amount of work that can be done in the out-of-order window. Without JIT there's just too many instructions for that to hide all that memory access latencies.