PostgreSQL 11 JIT Expression Compiler: Benchmark Results with TPC‑H Q1

Benchmark and TPC‑H

I enjoy running benchmarks while working on Citus Data to extend PostgreSQL, and the new JIT expression compiler in PostgreSQL 11 shows its best performance when queries contain many complex expressions (e.g., aggregates), read large amounts of data without I/O bottlenecks, and are computationally intensive.

Queries with multiple complex expressions such as aggregates.

Queries that read a lot of data but have no I/O resource shortage.

Queries that are so complex that they spend a lot of time in JIT compilation.

Queries that simply fetch information by primary‑key surrogate IDs are not ideal for showcasing the new JIT infrastructure.

The TPC‑H benchmark’s Q1 query is a good candidate for evaluating the impact of the new execution stack, and the specification can be found in the 137‑page PDF titled “TPC Benchmark™ H”.

Pricing Summary Report Query (Q1)

This query reports invoiced, shipped, and returned business volumes.

The query returns a summary pricing report for all order items shipped within a date range that falls 60‑120 days before the latest ship date in the database. It aggregates extended price, discount‑adjusted price, tax‑adjusted price, average quantity, average extended price, and average discount, grouping by RETURNFLAG and LINESTATUS and ordering by those columns. The count of line items per group is also included.

SQL example:

select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date '1998-12-01' - interval ':1' day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus :n -1 ;

Note: 1998‑12‑01 is the maximum possible ship date defined in the database population (ENDDATE‑30). The query includes all order items shipped before that date minus a configurable DELTA, chosen so that the query scans roughly 95‑97 % of the table rows.

Results

With a scale factor of 10, the database size is 22 GB including indexes. The full schema is available in tpch-schema.sql , and the indexes in tpch-pkeys.sql and tpch-index.sql .

In my tests, PostgreSQL 11 runs the TPC‑H Q1 query 29.31 % faster than PostgreSQL 10. Over a 10‑minute run, PostgreSQL 10 completed the query 21 times, while PostgreSQL 11 completed it 30 times.

The work by Andres Freund on the execution engine has a huge impact. The new engine revises expression evaluation to better utilize CPU cache lines and instruction pipelines. In this benchmark we disabled parallel query to isolate the effect of the new executor, though PostgreSQL 10 and 11 both support parallelism, which further improves query times.

PostgreSQL 11 also uses LLVM to compile SQL expressions to machine code during planning, providing an additional performance boost.

Tools

The benchmark specification provides two configuration files:

llvm‑q1‑infra.ini

Defines the AWS EC2 instance used for the test (c5.4xlarge with 30 GB RAM, suitable for the 22 GB dataset and indexes) and selects the Debian package from apt.postgresql.org that contains the PostgreSQL 11 development snapshot.

llvm‑q1‑schedule.ini

Defines the benchmark schedule:

[schedule]
full   = initdb, single-user-stream, multi-user-stream

The schedule runs an initdb phase loading the scale‑factor‑10 data with eight concurrent processes, followed by a single‑user stream (single CPU) and a multi‑user stream (all eight CPUs) each lasting ten minutes.

The benchmark tool is open‑source and can be obtained from https://github.com/dimitri/tpch-citus . It automatically runs TPCH on dynamic AWS EC2 infrastructure, supports direct loading via COPY protocol, and includes a C implementation of TPCH for PostgreSQL.

Looking Ahead

PostgreSQL 11 introduces a new execution engine that, with LLVM, compiles SQL code to native machine code, offering substantial benefits for expensive queries that scan many rows and repeatedly evaluate expressions.

To help achieve the best PostgreSQL 11 release, users are encouraged to test beta versions in CI environments, report any bugs or performance regressions, and provide reproducible steps. Details are available in the PostgreSQL 10.5 and 11 Beta 3 release notes.

In our benchmark, PostgreSQL 11’s JIT delivers up to a 29.31 % speed improvement, reducing the TPC‑H Q1 execution time from about 29 seconds on PostgreSQL 10 (scale factor 10) to roughly 20.5 seconds on PostgreSQL 11.

Citus has been busy testing the Citus extension against PostgreSQL 11 for months; because Citus is a pure extension rather than a fork, upgrading to PostgreSQL 11 will automatically grant all its new advantages.

Original article: https://www.citusdata.com/blog/2018/09/11/postgresql-11-just-in-time/