JIT compiler for expressions - Mailing list pgsql-hackers

Hello hackers,

We'd like to present our work on adding LLVM JIT compilation of expressions in SQL queries for PostgreSQL. The source code (based on 9.6.1) along with brief manual is available at our github: https://github.com/ispras/postgres . Сurrent speedup for TPC-H Q1 is 20% (on 40GB workload). Please feel free to test it and tell us what you think.

Currently, our JIT is used to compile expressions in every query, so for short-running queries JIT compilation may take longer than query execution itself. We plan to address it by using planner estimate to decide whether it worth JIT compiling, also we may try parallel JIT compilation. But for now we recommend testing it on a large workload  in order to pay off the compilation (we've tested on 40GB database for Q1).

The changes in PostgreSQL code itself are rather small, while the biggest part of new code in our repository is autogenerated (it's LLVM IR generators for PostgreSQL backend functions). The only real reason for shipping prebuild_llvm_backend.cpp is that it takes patched LLVM version to generate, otherwise it's generated right from PostgreSQL source code (please see more on automatic backend generation at our github site). With pre-generated cpp file, building our github PGSQL version w/JIT requires only clean, non-patched LLVM 3.7.

JIT compilation was tested on Linux, and currently we have 5 actual tests failing (which results in 24 errors in a regtest). It requires LLVM 3.7 (3.7.1) as build dependency (you can specify path to proper llvm-config with --with-llvm-config= configure option, e.g. it could be named llvm-config-3.7 on your system). Mac support is highly experimental, and wasn't tested much, but if you like to give it a try, you can do it with LLVM 3.7 from MacPorts or Homebrew.

This work is a part of our greater effort on implementing full JIT compiler in PostgreSQL, where along with JITting expressions we've changed the iteration model from Volcano-style to push-model and reimplemented code generation with LLVM for most of Scan/Aggregation/Join methods. That approach gives much better speedup (x4-5 times on Q1), but it takes many code changes, so we're developing it as PostgreSQL extension. It's not ready for release yet, but we're now working on performance, compatibility, as well as how to make it easier to maintain by making it possible to build both JIT compiler and the interpreter from the same source code. More information about our full JIT compiler and related work is available in presentation at LLVM Cauldron (http://llvm.org/devmtg/2016-09/slides/Melnik-PostgreSQLLLVM.pdf ) and PGCon (https://www.pgcon.org/2016/schedule/attachments/411_ISPRAS%20LLVM+Postgres%20Presentation.pdf ).