Proceedings of ISP RAS

Dynamic compilation of SQL queries for PostgreSQL

R.A. Buchatskiy (ISP RAS, Moscow, Russia)
E.Y. Sharygin (ISP RAS, Moscow, Russia, MSU, Moscow, Russia)
L.V. Skvortsov (MSU, Moscow, Russia)
R.A. Zhuykov (ISP RAS, Moscow, Russia)
D.M. Melnik (ISP RAS, Moscow, Russia)
R.V. Baev (MIPT, Dolgoprudny, Russia)


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


Proceedings of the Institute for System Programming, vol. 28, issue 6, 2016, pp. 37-48.

ISSN 2220-6426 (Online), ISSN 2079-8156 (Print).

DOI: 10.15514/ISPRAS-2016-28(6)-3

For citation

R.A. Buchatskiy, E.Y. Sharygin, L.V. Skvortsov, R.A. Zhuykov, D.M. Melnik, R.V. Baev Dynamic compilation of SQL queries for PostgreSQL. Proceedings of the Institute for System Programming, vol. 28, issue 6, 2016, pp. 37-48. DOI: 10.15514/ISPRAS-2016-28(6)-3.

Full text of the paper in pdf (in Russian) Back to the contents of the volume