Dynamic compilation of SQL queries for PostgreSQL

In recent years, as performance and capacity of main and external memory grow, performance of database management systems (DBMSes) on certain kinds of queries is more determined by raw CPU speed. Currently, PostgreSQL uses the interpreter to execute SQL queries. This yields an overhead caused by indirect calls to handler functions and runtime checks, which could be avoided if the query were compiled into native code "on-the-fly", i.e. just-in-time (JIT) compiled: at run time the specific table structure is known as well as data types and built-in functions used in the query as well as the query itself. This is especially important for complex queries, performance of which is CPU-bound. We have developed a PostgreSQL extension that implements SQL query JIT compilation using LLVM compiler infrastructure. In this paper we show how to implement LLVM-analogues of the main operators of the PostgreSQL, how to replace Volcano iterator model abstraction (open(), next(), close()) by the abstraction that is more suitable to generate code for a particular query. Currently, with LLVM JIT we achieve up to 4.3x speedup on TPC-H Q1 query as compared to original PostgreSQL interpreter.

dynamic compilation, just-in-time compilation, database management system engines, PostgreSQL, LLVM, query languages

1. Neumann T. Efficiently compiling efficient query plans for modern hardware. Proc. VLDB Endow. , vol. 4, no. 9, pp. 539–550, Jun. 2011.

2. Neumann T., Leis V. Compiling Database Queries into Machine Code. IEEE Data Engineering Bulletin, March 2014.

3. Lattner C. LLVM: An Infrastructure for Multi-Stage Optimization. Master’s thesis, Computer Science Dept., University of Illinois at Urbana-Champaign, Urbana, IL.

4. HyPer – A Hybrid OLTP&OLAP High Performance DBMS, http://www.hyper-db.de

5. PostgreSQL, an open source object-relational database system. https://www.postgresql.org

6. Vitesse DB website. http://vitessedata.com/vitesse-db

7. TPC-H, an ad-hoc, decision support benchmark. http://www.tpc.org/tpch/

8. Butterstein D., Grust T. Precision Performance Surgery for PostgreSQL – LLVM-based Expression Compilation, Just in Time. Proceedings of the 42nd Int'l Conference on Very Large Databases (VLDB 2016), New Delhi, India, August 2016.

9. Graefe G. Volcano – an extensible and parallel query evaluation system. IEEE Trans. Knowl. Data Eng., 6(1): 120–135, 1994.

10. MCJIT Design and Implementation. http://llvm.org/docs/MCJITDesignAndImplementation.html

11. Sharygin E.Y., Buchatskiy R.A, Skvortsov L.V., Zhuykov R.A., Melnik D.M. Dynamic compilation of expressions in SQL queries for PostgreSQL. Trudy ISP RAN/Proc. ISP RAS, vol. 28, issue 4, 2016, pp. 217-240 (in Russian). DOI: 10.15514/ISPRAS-2016-28(4)-13

12. clang – a C language family frontend for LLVM, http://clang.llvm.org/

13. opt – modular LLVM optimizer and analyzer, http://llvm.org/releases/3.7.1/docs/CommandGuide/opt.html

14. llc – LLVM static compiler, http://llvm.org/releases/3.7.1/docs/CommandGuide/llc.html

15. PostgreSQL with JIT compiler for expressions – project source code at github.com website. https://github.com/ispras/postgres/tree/llvm-expressions