4 ms·

Woah, the JIT for queries sounds really interesting! I'm curious what that will do for performance, especially for linear scans of a large number of rows, which

Woah, the JIT for queries sounds really interesting! I'm curious what that will do for performance, especially for linear scans of a large number of rows, which I imagine is where you would see the most improvement. 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.

jakobegger 8y ago
I’d assume that most queries are limited by memory / disk access time, so for many queries the savings will be marginal. But I imagine that it could make complex aggregates a lot faster?
- openasocket 8y ago
  True, I guess you'd see the advantage most when there is a complex WHERE clause or some complex projections and aggregates. Also, looking here https://www.pgcon.org/2017/schedule/attachments/467_PGCon%202017-05-26%2015-00%20ISPRAS%20Dynamic%20Compilation%20of%20SQL%20Queries%20in%20PostgreSQL%20Using%20LLVM%20JIT.pdf https://www.pgcon.org/2017/schedule/attachments/467_PGCon%20... it seems they can JIT the process for creating an index, which has some modest improvements. I imagine there are plenty of things like that that could also benefit from compilation.
  - anarazel 8y ago
    > t seems they can JIT the process for creating an index, which has some modest improvements We could, but currently don't yet. Not enough time / too short days...
- anarazel 8y ago
  > I’d assume that most queries are limited by memory / disk access time 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.
dboreham 8y ago
Compiling to either a P-code, or to native code, for query execution is quite an old technique. It is generally only a win under certain conditions, like most optimizations.
cryptonector 8y ago
SQLite3 compiles to bytecode, and would be a very good candidate for JITting the bytecode.
petereisentraut 8y ago
Now is the time to test this. From my early experiments, you can expect to get speedups for queries that last longer than a few seconds. A lot depends on whether I/O, caching, etc. dominates. The only disadvantage is that it takes time to do the JIT compilation, so if the query is already fast, JIT compilation will probably make it slower. Hence, there are ways to adjust when it should be used.
anarazel 8y ago
> Woah, the JIT for queries sounds really interesting! 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.

jakobegger 8y ago

I’d assume that most queries are limited by memory / disk access time, so for many queries the savings will be marginal. But I imagine that it could make complex aggregates a lot faster?

openasocket 8y ago
True, I guess you'd see the advantage most when there is a complex WHERE clause or some complex projections and aggregates. Also, looking here https://www.pgcon.org/2017/schedule/attachments/467_PGCon%202017-05-26%2015-00%20ISPRAS%20Dynamic%20Compilation%20of%20SQL%20Queries%20in%20PostgreSQL%20Using%20LLVM%20JIT.pdf https://www.pgcon.org/2017/schedule/attachments/467_PGCon%20... it seems they can JIT the process for creating an index, which has some modest improvements. I imagine there are plenty of things like that that could also benefit from compilation.
- anarazel 8y ago
  > t seems they can JIT the process for creating an index, which has some modest improvements We could, but currently don't yet. Not enough time / too short days...
anarazel 8y ago
> I’d assume that most queries are limited by memory / disk access time 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.

dboreham 8y ago

Compiling to either a P-code, or to native code, for query execution is quite an old technique. It is generally only a win under certain conditions, like most optimizations.

cryptonector 8y ago

SQLite3 compiles to bytecode, and would be a very good candidate for JITting the bytecode.

petereisentraut 8y ago

Now is the time to test this. From my early experiments, you can expect to get speedups for queries that last longer than a few seconds. A lot depends on whether I/O, caching, etc. dominates. The only disadvantage is that it takes time to do the JIT compilation, so if the query is already fast, JIT compilation will probably make it slower. Hence, there are ways to adjust when it should be used.

anarazel 8y ago

> Woah, the JIT for queries sounds really interesting! 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.