blog
An Overview of Just-in-Time Compilation (JIT) for PostgreSQL
Historically PostgreSQL has provided compilation features in the form of ahead-of-time compilation for PL/pgSQL functions and version 10 introduced expression compilation. None of those generate machine code though.
JIT for SQL was discussed many years ago, and for PostgreSQL the feature is the result of a substantial code change.
To check if PostgreSQL binary was built with LLVM support use the pg_configure command to display the compile flags and look for –with-llvm in the output. Example for the PGDG RPM distribution:
omiday ~ $ /usr/pgsql-11/bin/pg_config --configure
'--enable-rpath' '--prefix=/usr/pgsql-11' '--includedir=/usr/pgsql-11/include' '--mandir=/usr/pgsql-11/share/man' '--datadir=/usr/pgsql-11/share' '--enable-tap-tests' '--with-icu' '--with-llvm' '--with-perl' '--with-python' '--with-tcl' '--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam' '--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/lib64' '--enable-nls' '--enable-dtrace' '--with-uuid=e2fs' '--with-libxml' '--with-libxslt' '--with-ldap' '--with-selinux' '--with-systemd' '--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql' '--docdir=/usr/pgsql-11/doc' '--htmldir=/usr/pgsql-11/doc/html' 'CFLAGS=-O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection' 'PKG_CONFIG_PATH=:/usr/lib64/pkgconfig:/usr/share/pkgconfig'Why LLVM JIT?
It all started about two years ago as explained in Adres Freund’s post when expression evaluation and tuple deforming proved to be the roadblocks in speeding up large queries. After adding the JIT implementation “expression evaluation itself is more than ten times faster than before” in Andres’ words. Further, the Q&A section ending his post explains the choice of LLVM over other implementations.
While LLVM was the chosen provider the GUC parameter jit_provider can be used to point to another JIT provider. Note though that inlining support is only available when using the LLVM provider, due to the way the build process works.
When to JIT?
The documentation is clear: long running queries that are CPU bound will benefit from JIT compilation. In addition the mailing list discussions referenced throughout this blog point out that JIT is too expensive for queries that get executed only once.
Compared to programming languages, PostgreSQL has the advantage of “knowing” when to JIT, by relying on the query planner. To that effect a number of GUC parameters were introduced. To protect users from negative surprises when enabling JIT the cost related parameters are intentionally set to reasonably high values. Note that setting the JIT cost parameters to ‘0’ will force all queries to be JIT-compiled and as a result slowing down all your queries.
While JIT can be generally beneficial there are cases when having it enabled can be detrimental as discussed in commit b9f2d4d3.
How to JIT?
As alluded above the RPM binary packages are LLVM-enabled. However, in order to have JIT compilation working a few additional steps are required:
To wit:
postgres@[local]:54311 test# show server_version;
server_version
----------------
11.1
(1 row)postgres@[local]:54311 test# show port;
port
-------
54311
(1 row)postgres@[local]:54311 test# create table t1 (id serial);
CREATE TABLE
postgres@[local]:54311 test# insert INTO t1 (id) select * from generate_series(1, 10000000);
INSERT 0 10000000
postgres@[local]:54311 test# set jit = 'on';
SET
postgres@[local]:54311 test# set jit_above_cost = 10; set jit_inline_above_cost = 10; set jit_optimize_above_cost = 10;
SET
SET
SET
postgres@[local]:54311 test# explain analyze select count(*) from t1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=97331.43..97331.44 rows=1 width=8) (actual time=647.585..647.585 rows=1 loops=1)
-> Gather (cost=97331.21..97331.42 rows=2 width=8) (actual time=647.484..649.059 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=96331.21..96331.22 rows=1 width=8) (actual time=640.995..640.995 rows=1 loops=3)
-> Parallel Seq Scan on t1 (cost=0.00..85914.57 rows=4166657 width=0) (actual time=0.060..397.121 rows=3333333 loops=3)
Planning Time: 0.182 ms
Execution Time: 649.170 ms
(8 rows)Note that I did enable JIT (which is disabled by default following the pgsql-hackers discussion referenced in commit b9f2d4d3). I also adjusted the cost of JIT parameters as suggested in the documentation.
The first hint is found in the src/backend/jit/README file referenced in the JIT documentation:
Which shared library is loaded is determined by the jit_provider GUC, defaulting to "llvmjit".Since the RPM package is not pulling in the JIT dependency automatically — as it was decided after entensive discussions (see the full thread) — we need to install it manually:
[root@omiday ~]# dnf install postgresql11-llvmjitOnce the installation completes we can test right away:
postgres@[local]:54311 test# explain analyze select count(*) from t1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=97331.43..97331.44 rows=1 width=8) (actual time=794.998..794.998 rows=1 loops=1)
-> Gather (cost=97331.21..97331.42 rows=2 width=8) (actual time=794.870..803.680 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=96331.21..96331.22 rows=1 width=8) (actual time=689.124..689.125 rows=1 loops=3)
-> Parallel Seq Scan on t1 (cost=0.00..85914.57 rows=4166657 width=0) (actual time=0.062..385.278 rows=3333333 loops=3)
Planning Time: 0.150 ms
JIT:
Functions: 4
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 2.146 ms, Inlining 117.725 ms, Optimization 47.928 ms, Emission 69.454 ms, Total 237.252 ms
Execution Time: 803.789 ms
(12 rows)We can also display the JIT details per worker:
postgres@[local]:54311 test# explain (analyze, verbose, buffers) select count(*) from t1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=97331.43..97331.44 rows=1 width=8) (actual time=974.352..974.352 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2592 read=41656
-> Gather (cost=97331.21..97331.42 rows=2 width=8) (actual time=974.166..980.942 rows=3 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
JIT for worker 0:
Functions: 2
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.378 ms, Inlining 74.033 ms, Optimization 11.979 ms, Emission 9.470 ms, Total 95.861 ms
JIT for worker 1:
Functions: 2
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.319 ms, Inlining 68.198 ms, Optimization 8.827 ms, Emission 9.580 ms, Total 86.924 ms
Buffers: shared hit=2592 read=41656
-> Partial Aggregate (cost=96331.21..96331.22 rows=1 width=8) (actual time=924.936..924.936 rows=1 loops=3)
Output: PARTIAL count(*)
Buffers: shared hit=2592 read=41656
Worker 0: actual time=900.612..900.613 rows=1 loops=1
Buffers: shared hit=668 read=11419
Worker 1: actual time=900.763..900.763 rows=1 loops=1
Buffers: shared hit=679 read=11608
-> Parallel Seq Scan on public.t1 (cost=0.00..85914.57 rows=4166657 width=0) (actual time=0.311..558.192 rows=3333333 loops=3)
Output: id
Buffers: shared hit=2592 read=41656
Worker 0: actual time=0.389..539.796 rows=2731662 loops=1
Buffers: shared hit=668 read=11419
Worker 1: actual time=0.082..548.518 rows=2776862 loops=1
Buffers: shared hit=679 read=11608
Planning Time: 0.207 ms
JIT:
Functions: 9
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 8.818 ms, Inlining 153.087 ms, Optimization 77.999 ms, Emission 64.884 ms, Total 304.787 ms
Execution Time: 989.360 ms
(36 rows)The JIT implementation can also takes advantage of the parallel query execution feature. To exemplify, first let’s disable parallelization:
postgres@[local]:54311 test# set max_parallel_workers_per_gather = 0;
SET
postgres@[local]:54311 test# explain analyze select count(*) from t1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=169247.71..169247.72 rows=1 width=8) (actual time=1447.315..1447.315 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..144247.77 rows=9999977 width=0) (actual time=0.064..957.563 rows=10000000 loops=1)
Planning Time: 0.053 ms
JIT:
Functions: 2
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.388 ms, Inlining 1.359 ms, Optimization 7.626 ms, Emission 7.963 ms, Total 17.335 ms
Execution Time: 1447.783 ms
(8 rows)The same command with parallel queries enabled completes in half the time:
postgres@[local]:54311 test# reset max_parallel_workers_per_gather ;
RESET
postgres@[local]:54311 test# explain analyze select count(*) from t1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=97331.43..97331.44 rows=1 width=8) (actual time=707.126..707.126 rows=1 loops=1)
-> Gather (cost=97331.21..97331.42 rows=2 width=8) (actual time=706.971..712.199 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=96331.21..96331.22 rows=1 width=8) (actual time=656.102..656.103 rows=1 loops=3)
-> Parallel Seq Scan on t1 (cost=0.00..85914.57 rows=4166657 width=0) (actual time=0.067..384.207 rows=3333333 loops=3)
Planning Time: 0.158 ms
JIT:
Functions: 9
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 3.709 ms, Inlining 142.150 ms, Optimization 50.983 ms, Emission 33.792 ms, Total 230.634 ms
Execution Time: 715.226 ms
(12 rows)I found interesting to compare the results from the tests discussed in this post, during the initial stages of JIT implementation versus the final version. First make sure the conditions in the original test are met i.e. the database must fit in memory:
postgres@[local]:54311 test# l+
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8027 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7889 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7889 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 2763 MB | pg_default |
postgres@[local]:54311 test# show shared_buffers ;
3GB
Time: 0.485 msRun the tests with JIT disabled:
postgres@[local]:54311 test# set jit = off;
SET
Time: 0.483 ms
postgres@[local]:54311 test# select sum(c8) from t1;
0
Time: 1036.231 ms (00:01.036)
postgres@[local]:54311 test# select sum(c2), sum(c3), sum(c4), sum(c5),
sum(c6), sum(c7), sum(c8) from t1;
0 | 0 | 0 | 0 | 0 | 0 | 0
Time: 1793.502 ms (00:01.794)Next run the tests with JIT enabled:
postgres@[local]:54311 test# set jit = on; set jit_above_cost = 10; set
jit_inline_above_cost = 10; set jit_optimize_above_cost = 10;
SET
Time: 0.473 ms
SET
Time: 0.267 ms
SET
Time: 0.204 ms
SET
Time: 0.162 ms
postgres@[local]:54311 test# select sum(c8) from t1;
0
Time: 795.746 ms
postgres@[local]:54311 test# select sum(c2), sum(c3), sum(c4), sum(c5),
sum(c6), sum(c7), sum(c8) from t1;
0 | 0 | 0 | 0 | 0 | 0 | 0
Time: 1080.446 ms (00:01.080)That is a speedup of about 25% for the first test case and 40% for the second!
Lastly, it’s important to remember that for prepared statements, the JIT compilation is performed when the function is first executed.
Conclusion
By default, JIT compilation is disabled, and for RPM based systems the installer will not hint about the need to install the JIT package providing the bitcode for the default provider LLVM.
When building from sources pay attention to the compile flags in order to avoid performance issues, for example if LLVM assertions are enabled.
As discussed on the pgsql-hackers list the JIT impact on costing is not yet fully understood so careful planning is required before enabling the feature cluster wide, as queries that could otherwise benefit from compilation may actually run slower. JIT can be, however, enabled on a per query basis.
For in-depth information on the implementation of the JIT compilation review the project Git logs, the Commitfests, and the pgsql-hackers mail thread.
Happy JITing!
