Postgres has a feature called JIT compilation. This helps execute CPU-intensive queries faster. During complex query execution, it goes through different parts of the query step by step during execution, and it converts certain sections into machine code using LLVM.
The biggest benefits of JIT come with running queries. When you're working with datasets, complicated expressions, lots of aggregations, or many joins, JIT makes a real difference. It cuts down on CPU usage. Speeds things up, in a big way.
For small queries, the compilation process can take more time than it saves. In those cases, the overhead of JIT might actually slow things down instead of helping. So it's not always better to use JIT, especially when the query is short and simple.
In the psql terminal, when you enter “show jit” and press the Tab key, you can see nearly 10 custom parameters related to JIT.
postgres=# show jit
jit jit_debugging_support jit_expressions jit_optimize_above_cost jit_provider
jit_above_cost jit_dump_bitcode jit_inline_above_cost jit_profiling_support jit_tuple_deforming
Now let’s look at each jit parameter in more detail.
1. jit
show jit;
Result:
jit
-----
off
(1 row)
Check the metadata from pg_settings like this.
select * from pg_settings where name = 'jit';
Result:
-[ RECORD 1 ]---+-------------------------------------------------
name | jit
setting | off
unit |
category | Query Tuning / Other Planner Options
short_desc | Allow JIT compilation.
extra_desc |
context | user
vartype | bool
source | configuration file
min_val |
max_val |
enumvals |
boot_val | on
reset_val | off
sourcefile | /var/lib/postgresql/18/main/postgresql.auto.conf
sourceline | 8
pending_restart | f
Purpose:
This enables or disables the use of jit compilation in postgres.
2. jit_above_cost
show jit_above_cost;
Result:
-[ RECORD 1 ]--+-------
jit_above_cost | 100000
Check the metadata from pg_settings like this.
select * from pg_settings where name = 'jit_above_cost';
Result:
-[ RECORD 1 ]---+----------------------------------------------------
name | jit_above_cost
setting | 100000
unit |
category | Query Tuning / Planner Cost Constants
short_desc | Perform JIT compilation if query is more expensive.
extra_desc | -1 disables JIT compilation.
context | user
vartype | real
source | default
min_val | -1
max_val | 1.79769e+308
enumvals |
boot_val | 100000
reset_val | 100000
sourcefile |
sourceline |
pending_restart | f
Purpose:
It determines the minimum estimated planner cost before using the jit compilation in the postgres query execution. If the planner estimates the query cost is greater than jit_above_cost, then JIT is used.
Otherwise, postgres skips JIT because the compilation cost is not worthwhile.
3. show jit_debugging_support;
show jit_debugging_support;
Result:
-[ RECORD 1 ]---------+----
jit_debugging_support | off
Check the metadata from pg_settings like this.
select * from pg_settings where name = 'jit_debugging_support';
Result:
-[ RECORD 1 ]---+-----------------------------------------------
name | jit_debugging_support
setting | off
unit |
category | Developer Options
short_desc | Register JIT-compiled functions with debugger.
extra_desc |
context | superuser-backend
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
Purpose:
jit_debugging_support allows Postgres to register LLVM-generated JIT machine code with native debuggers such as gdb. This lets developers inspect the generated code, set breakpoints, and obtain meaningful stack traces while debugging JIT-compiled queries. It is intended only for JIT development and debugging, not for normal database operation.
4. show jit_dump_bitcode;
show jit_dump_bitcode;
Result:
-[ RECORD 1 ]----+----
jit_dump_bitcode | off
Check the metadata from pg_settings like this.
select * from pg_settings where name = 'jit_dump_bitcode';
Result:
-[ RECORD 1 ]---+----------------------------------------------------
name | jit_dump_bitcode
setting | off
unit |
category | Developer Options
short_desc | Write out LLVM bitcode to facilitate JIT debugging.
extra_desc |
context | superuser
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
Purpose:
jit_dump_bitcode instructs the postgres to save the LLVM bitcode generated during JIT compilation to files. These files can be examined with LLVM tools to analyze, debug, or verify the generated code before it is converted into native machine instructions. It is intended for JIT development and troubleshooting, not for normal production use.
5. jit_expressions
show jit_expressions ;
Result:
-[ RECORD 1 ]---+---
jit_expressions | on
Check the metadata from pg_settings like this.
select * from pg_settings where name = 'jit_expressions'
Result:
-[ RECORD 1 ]---+--------------------------------------
name | jit_expressions
setting | on
unit |
category | Developer Options
short_desc | Allow JIT compilation of expressions.
extra_desc |
context | user
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
Purpose:
jit_expressions enables jit compilation of SQL expression evaluation, such as arithmetic operations, comparisons, function calls, and predicates. Instead of interpreting these expressions row by row, postgres generates native machine code to execute them more efficiently, reducing CPU overhead for expensive queries.
6. jit_inline_above_cost
show jit_inline_above_cost;
Result:
-[ RECORD 1 ]---------+-------
jit_inline_above_cost | 500000
Check the metadata from pg_settings like this.
select * from pg_settings where name = 'jit_inline_above_cost';
Result:
-[ RECORD 1 ]---+-------------------------------------------------
name | jit_inline_above_cost
setting | 500000
unit |
category | Query Tuning / Planner Cost Constants
short_desc | Perform JIT inlining if query is more expensive.
extra_desc | -1 disables inlining.
context | user
vartype | real
source | default
min_val | -1
max_val | 1.79769e+308
enumvals |
boot_val | 500000
reset_val | 500000
sourcefile |
sourceline |
pending_restart | f
Purpose:
jit_inline_above_cost specifies the minimum estimated query cost at which postgres allows LLVM to inline small functions during jit compilation. Function inlining removes function call overhead and can produce faster generated code, but it increases jit compilation time. Queries with costs below this threshold skip this optimization.
7. jit_optimize_above_cost
show jit_optimize_above_cost;
Result:
-[ RECORD 1 ]-----------+-------
jit_optimize_above_cost | 500000
Check the metadata from pg_settings like this.
select * from pg_settings where name = 'jit_optimize_above_cost';
Result:
-[ RECORD 1 ]---+------------------------------------------------------------
name | jit_optimize_above_cost
setting | 500000
unit |
category | Query Tuning / Planner Cost Constants
short_desc | Optimize JIT-compiled functions if query is more expensive.
extra_desc | -1 disables optimization.
context | user
vartype | real
source | default
min_val | -1
max_val | 1.79769e+308
enumvals |
boot_val | 500000
reset_val | 500000
sourcefile |
sourceline |
pending_restart | f
Purpose:
jit_optimize_above_cost specifies the minimum estimated query cost at which postgres applies expensive LLVM optimization passes to jit generated code. These optimizations can improve execution performance for costly queries but increase jit compilation time. Queries with costs below this threshold skip these optimizations.
8. jit_profiling_support
show jit_profiling_support;
Result:
-[ RECORD 1 ]---------+----
jit_profiling_support | off
Check the metadata from pg_settings like this.
select * from pg_settings where name = 'jit_profiling_support';
Result:
-[ RECORD 1 ]---+----------------------------------------------------
name | jit_profiling_support
setting | off
unit |
category | Developer Options
short_desc | Register JIT-compiled functions with perf profiler.
extra_desc |
context | superuser-backend
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
Purpose:
jit_profiling_support enables postgres to expose jit generated machine code to profiling tools such as perf. This allows profilers to attribute CPU time to JIT-compiled functions, making performance analysis of JIT-compiled queries more accurate. It is intended for performance profiling and debugging, not for normal production use.
9. jit_provider
show jit_provider;
Result:
-[ RECORD 1 ]+--------
jit_provider | llvmjit
Check the metadata from pg_settings like this.
select * from pg_settings where name = 'jit_provider';
Result:
-[ RECORD 1 ]---+-------------------------------------------------------
name | jit_provider
setting | llvmjit
unit |
category | Client Connection Defaults / Shared Library Preloading
short_desc | JIT provider to use.
extra_desc |
context | postmaster
vartype | string
source | default
min_val |
max_val |
enumvals |
boot_val | llvmjit
reset_val | llvmjit
sourcefile |
sourceline |
pending_restart | f
Purpose:
jit_provider specifies the JIT provider library that postgres uses to perform just-in-time compilation. By default, it is set to llvmjit, which uses LLVM to generate native machine code for query execution. This parameter is primarily intended for selecting or testing different jit provider implementations.
10. jit_tuple_deforming
show jit_tuple_deforming;
Result:
-[ RECORD 1 ]-------+---
jit_tuple_deforming | on
Check the metadata from pg_settings like this.
select * from pg_settings where name = 'jit_tuple_deforming';
Result:
-[ RECORD 1 ]---+------------------------------------------
name | jit_tuple_deforming
setting | on
unit |
category | Developer Options
short_desc | Allow JIT compilation of tuple deforming.
extra_desc |
context | user
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
Purpose:
jit_tuple_deforming enables JIT compilation of tuple deforming, the process of extracting individual column values from table rows. By generating native machine code for this operation, PostgreSQL reduces CPU overhead when processing large numbers of rows. This can improve the performance of CPU-intensive queries.
JIT compilation can significantly improve the performance of CPU-intensive queries, but it is not a feature that should be enabled or tuned without understanding its behaviour. Each JIT parameter serves a specific purpose, whether it controls when compilation begins, enables additional optimizations, or provides debugging and profiling capabilities. Knowing what these parameters do helps you make informed tuning decisions and avoid unnecessary compilation overhead. By selecting the right settings for your workload, you can achieve a better balance between query execution speed and compilation cost.