March 25, 2025
Summary: In this tutorial, you will learn how to tune SQL queries using just-in-time compilation (JIT) in PostgreSQL.
Table of Contents
Introduction
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.
To check if PostgreSQL binary was built with LLVM support use the pg_config command to display the compile flags and look for --with-llvm in the output. Example for the PGDG RPM distribution:
$ /usr/pgsql-15/bin/pg_config --configure
'--enable-rpath' '--prefix=/usr/pgsql-15' '--includedir=/usr/pgsql-15/include' '--mandir=/usr/pgsql-15/share/man' '--datadir=/usr/pgsql-15/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-15/doc' '--htmldir=/usr/pgsql-15/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 started with a discussion in community when expression evaluation and tuple deforming proved to be the roadblocks in speeding up large queries. According to a community member’s words, after adding the JIT implementation “expression evaluation itself is more than ten times faster than before”s. Further, the Q&A section ending this 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 article 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 mailing list.
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:
show server_version;
server_version
----------------
15.1
(1 row)
show port;
port
-------
54311
(1 row)
create table t1 (id serial);
insert INTO t1 (id) select * from generate_series(1, 10000000);
set jit = 'on';
set jit_above_cost = 10;
set jit_inline_above_cost = 10;
set jit_optimize_above_cost = 10;
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 mailing list). 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:
HINT: 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 extensive discussions (see the full thread) — we need to install it manually:
dnf install postgresql15-llvmjit
Once the installation completes we can test right away:
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:
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:
set max_parallel_workers_per_gather = 0;
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:
reset max_parallel_workers_per_gather;
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)
Let’s take a look at how JIT compilation performs. Run the tests with JIT disabled:
set jit = off;
select sum(id) from t1;
sum
----------------
50000005000000
(1 row)
Time: 1036.231 ms
Next run the tests with JIT enabled:
set jit = on;
set jit_above_cost = 10;
set jit_inline_above_cost = 10;
set jit_optimize_above_cost = 10;
select sum(id) from t1;
sum
----------------
50000005000000
(1 row)
Time: 795.746 ms
That is a speedup of about 25% for the test case!
Lastly, it’s important to remember that for prepared statements, the JIT compilation is performed when the function is executed at first time.
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.