Efficiently compiling efficient query plans for modern hardware

Abstract

As main memory grows, query performance is more and more determined by the raw CPU costs of query processing itself. The classical iterator style query processing technique is very simple and flexible, but shows poor performance on modern CPUs due to lack of locality and frequent instruction mispredictions. Several techniques like batch oriented processing or vectorized tuple processing have been proposed in the past to improve this situation, but even these techniques are frequently out-performed by hand-written execution plans.In this work we present a novel compilation strategy that translates a query into compact and efficient machine code using the LLVM compiler framework. By aiming at good code and data locality and predictable branch layout the resulting code frequently rivals the performance of handwritten C++ code. We integrated these techniques into the HyPer main memory database system and show that this results in excellent query performance while requiring only modest compilation time.

CPU and cache efficient management of memory-resident databases

“…As demonstrated in previous work [27], the generated code achieves the CPU efficiency of the bulk processing model without the need for expensive intermediate materialization.…”

“…The resulting performance (independent of the storage model) of the Volcano model indicates that it is, indeed, inappropriate for such a query on memory-resident data. The JiT-compiled query was implemented according to the HyPer compilation model [27] and is displayed in Figure 2c in the version that is executed on the PDSM data. 1 The partially decomposed representation was hand-optimized…”

“…To compete with the bulk processing model in terms of CPU efficiency, HyPer relies on JiT-compilation of queries [27]. Whilst DBMS compilers have a long history [3], [8], up to recently [27], [24], [31], the focus has been flexibility and extensibility rather than performance. The idea is to generate code that is directly executable on the host system's CPU.…”

“…The code generation process is described in previous work [27] and out of scope of this paper. To give an impression of the generated code, however, Figure 2 illustrates the translation of the relational algebra plan of the example query (Figure 2a) to C99-code.…”

“…Thus, generating the access pattern for a given physical query plan is similar to generating the actual code to perform the query (see [27] for a description). To generate the access pattern, the relational query plan is traversed in pre-order from its root (see Figure 7) and the appropriate patterns emitted according to Table II.…”