3 ms·

This is really cool. I've often wondered why queries weren't be compiled to native code to remove interpreter overhead. I imagine that overhead must not have be

This is really cool. I've often wondered why queries weren't be compiled to native code to remove interpreter overhead. I imagine that overhead must not have been particularly low hanging fruit until recently.

anarazel 9y ago
> I've often wondered why queries weren't be compiled to native code to remove interpreter overhead. It's not entire queries that are JITed at this point, it's just a subset of the work. WHERE clauses are, but the logic around joins, just as an example, are not. Partially because it's a lot of work to do more, partially because it's often not very beneficial and you end up spending more time emitting & optimizing JITed code than you save. > I imagine that overhead must not have been particularly low hanging fruit until recently. I think it's largely because it's a huge project. I've spent a good chunk of two release cycles (a year each) on it. Not that easy to get an employer (or two as in my case) to sponsor work of that size. Thankfully both Citus and EnterpriseDB were willing to do so. Hopefully now that the infrastructure is in we can JIT more things in smaller patches.
jandrewrogers 9y ago
Actually, the performance benefits can be huge (design dependent) and have been for a long time. The reasons have more to do with complexity and difficulty of implementation. The query compilation and execution engine must preserve the operational robustness, integrity, and behavior of the database engine, which is not a trivial design and implementation problem. The number of things that can go horribly wrong increase significantly versus an interpreter. JIT query compilers were relatively common in closed source databases even a decade ago, it was one of their technical advantages versus open source. This is a necessary step toward closing that gap. There is a similar gap when it comes to storage/execution schedulers that is currently lacking in open source implementations which has a similar level of impact.
- barrkel 9y ago
  As an example of something that can go wrong: Apache Impala uses LLVM to JIT queries. Turns out some code optimization algorithms are O(n^3) (or worse) or so, where n is small enough not to matter too much with human code, but falls apart with a few thousand columns in a single table, not super unusual with analytics in an environment with expensive joins like Hadoop.

anarazel 9y ago

> I've often wondered why queries weren't be compiled to native code to remove interpreter overhead. It's not entire queries that are JITed at this point, it's just a subset of the work. WHERE clauses are, but the logic around joins, just as an example, are not. Partially because it's a lot of work to do more, partially because it's often not very beneficial and you end up spending more time emitting & optimizing JITed code than you save. > I imagine that overhead must not have been particularly low hanging fruit until recently. I think it's largely because it's a huge project. I've spent a good chunk of two release cycles (a year each) on it. Not that easy to get an employer (or two as in my case) to sponsor work of that size. Thankfully both Citus and EnterpriseDB were willing to do so. Hopefully now that the infrastructure is in we can JIT more things in smaller patches.

jandrewrogers 9y ago

Actually, the performance benefits can be huge (design dependent) and have been for a long time. The reasons have more to do with complexity and difficulty of implementation. The query compilation and execution engine must preserve the operational robustness, integrity, and behavior of the database engine, which is not a trivial design and implementation problem. The number of things that can go horribly wrong increase significantly versus an interpreter. JIT query compilers were relatively common in closed source databases even a decade ago, it was one of their technical advantages versus open source. This is a necessary step toward closing that gap. There is a similar gap when it comes to storage/execution schedulers that is currently lacking in open source implementations which has a similar level of impact.

barrkel 9y ago
As an example of something that can go wrong: Apache Impala uses LLVM to JIT queries. Turns out some code optimization algorithms are O(n^3) (or worse) or so, where n is small enough not to matter too much with human code, but falls apart with a few thousand columns in a single table, not super unusual with analytics in an environment with expensive joins like Hadoop.