In PostgreSQL 18, why can JIT compilation make a short query slower, and which settings decide whether a query is JIT-compiled?
answer
- estimated cost is the trigger
- three thresholds, three stages
- every process compiles its own
- JIT section in EXPLAIN ANALYZE
basics
~20 sJIT starts when a plan's estimated total cost exceeds jit_above_cost (100000), with inlining and optimization above 500000. A short query the planner overestimates pays compile time that can exceed its runtime; jit = off or higher thresholds fix it.
solid answer
~50 sPostgreSQL 18 ships with `jit = on`, and a server built with LLVM compiles expression evaluation and tuple deforming to native code. The decision is made at plan time from the plan's total estimated cost: above `jit_above_cost` (100000) it compiles, above `jit_inline_above_cost` (500000) it also inlines small functions, and above `jit_optimize_above_cost` (500000) it runs expensive LLVM optimizations. Because the trigger is an estimate, a query the planner overestimates, typically after a bad row estimate, can spend more time compiling than executing, and each parallel worker compiles its own code. `EXPLAIN (ANALYZE)` shows a `JIT:` section with the function count and a timing breakdown, which I compare against the execution time. For OLTP I set `jit = off` or raise `jit_above_cost` for that database or role, keep JIT for analytics, and fix the misestimate behind the inflated cost.
code
sql · 16 lines-- compare with and without JIT in one session
EXPLAIN (ANALYZE)
SELECT tier, count(*), sum(points)
FROM loyalty_ledger l JOIN members m ON m.id = l.member_id
WHERE l.earned_at >= now() - interval '1 day'
GROUP BY tier;
SET jit = off;
EXPLAIN (ANALYZE)
SELECT tier, count(*), sum(points)
FROM loyalty_ledger l JOIN members m ON m.id = l.member_id
WHERE l.earned_at >= now() - interval '1 day'
GROUP BY tier;
-- OLTP database: compile only very expensive plans
ALTER DATABASE loyalty SET jit_above_cost = 1000000;go deeper
Recall that PostgreSQL 18 has JIT on by default and that it compiles query expressions to native code for expensive queries.
Explain the three cost thresholds, that they compare against the planner's estimated cost, and how to read the JIT section of EXPLAIN ANALYZE.
Recognise JIT overhead on short queries, trace it to an inflated estimate, and turn JIT off or raise thresholds for the OLTP side only.
Decide where native compilation earns its cost across mixed workloads, and separate analytical and transactional roles so each gets the right setting.
## What PostgreSQL's JIT does **Just-in-time compilation** turns part of query execution into native machine code at run time. PostgreSQL's built-in provider uses **LLVM** and is available when the server was built with LLVM support; `jit_provider` (default `llvmjit`) selects it and only changes with a restart. Two kinds of work are compiled: - **expression evaluation**: `WHERE` clauses, target lists, aggregates and projections, compiled into code specific to each expression; - **tuple deforming**: turning an on-disk row into its in-memory form, compiled for the specific table layout and the columns needed. It does not speed up disk reads, index descents or planning. It pays off for long, CPU-bound queries, typically analytics that evaluate the same expressions over millions of rows. ## The settings | setting | default | crossing it adds | |---|---|---| | `jit` | on | the master switch | | `jit_above_cost` | 100000 | compilation of expressions and deforming | | `jit_inline_above_cost` | 500000 | inlining of small C and internal functions and operators | | `jit_optimize_above_cost` | 500000 | expensive LLVM optimization passes | Setting any threshold to `-1` disables that stage. The thresholds are compared with the **total estimated cost** of the plan, in the same units as `seq_page_cost` = 1.0. A cost of 100000 is roughly the planner's price for reading 100,000 pages sequentially, which a modest analytical query can reach. ## Why short queries suffer - **The trigger is an estimate.** If the planner overestimates rows, for example because of correlated columns or stale statistics, a query that really runs in milliseconds can carry an estimated cost above the threshold and get compiled. - **Compilation is real work.** Generating, optimizing and emitting code takes milliseconds per query and grows with the number of functions compiled. For a 5 ms query that can double or triple the response time, on every execution. - **Every process compiles.** In a parallel plan each worker compiles its own copy, so the overhead multiplies with the worker count. - **Decisions are made at plan time.** With a generic plan for a prepared statement, the thresholds in effect when the plan was made decide, not those at execution time. `jit = off` is different: it acts at both plan time and execution time. ## Diagnosing it 1. Run `EXPLAIN (ANALYZE)`. When JIT was used, a `JIT:` section lists `Functions`, the `Options` applied (Inlining, Optimization, Expressions, Deforming) and a `Timing` line with Generation, Inlining, Optimization, Emission and Total. 2. Compare `Timing ... Total` with `Execution Time`. If JIT takes a large share, it is not paying off. 3. Run the query again after `SET jit = off` in the same session and compare. 4. Look at estimated versus actual rows to see why the cost estimate crossed the threshold; that misestimate may hurt other plan choices too. 5. For a parallel plan, `EXPLAIN (ANALYZE, VERBOSE)` breaks the JIT timing out per worker. ## Remedies - **Turn it off where it never pays.** For an OLTP database or application role, `jit = off`, or a much higher `jit_above_cost`, removes the overhead; scoping a setting to a database or role is a parameter-management subject. - **Keep it for analytics.** Reporting roles that scan large tables may gain from JIT; a separate setting per role keeps both. - **Raise inlining and optimization thresholds** if plain compilation helps but the expensive stages do not. - **Fix the estimate.** Better statistics lower the inflated cost, which often removes the JIT trigger and improves the plan at the same time. ## Why one setting rarely fits both workloads An analytical query that aggregates tens of millions of rows evaluates the same expressions over and over, so a few hundred milliseconds of compilation can buy back seconds of execution. A transactional query that touches a handful of rows has almost nothing to amortise the compile step against, and it runs thousands of times a minute, so even a few milliseconds per execution adds up to real CPU. That is why the usual answer is not a single server-wide value but two: JIT off or a high threshold for the application's database or role, and the defaults, or lower thresholds, for the reporting role.
- A PostgreSQL EXPLAIN ANALYZE shows JIT Timing Total of 120 ms and an Execution Time of 130 ms. What do you conclude?Almost all the time went to compiling, so JIT is a net loss for this query. The plan's estimated cost crossed jit_above_cost, and probably the inlining and optimization thresholds, while the real work was small. Turn JIT off or raise the thresholds for this workload, and check estimated against actual rows to see why the cost was inflated.
- Does SET jit = off in PostgreSQL stop JIT for a prepared statement whose generic plan was built with JIT enabled?Yes. The manual says turning jit off has effects at both plan and execution time, so a cached plan does not compile once it is off. The cost thresholds behave differently: they are evaluated when the plan is made, so changing them later does not alter an existing generic plan's JIT decision.
- Why does a parallel PostgreSQL query pay JIT cost more than once?Each parallel worker is a separate process that executes its own copy of the parallel part of the plan, so each one generates and compiles its own code. The overhead grows with the number of workers, and EXPLAIN (ANALYZE, VERBOSE) shows the JIT timing for each worker separately.
- JIT starts when a query has already run longer than a time limit.
- JIT speeds up disk reads and index lookups.
- Parallel workers share one compiled copy of the query's code.
- Raising jit_above_cost makes JIT compilation more likely.
- JIT must first be installed with CREATE EXTENSION before it works.
Assumes PostgreSQL 18, where jit is on by default with thresholds of 100000 and 500000; check the default again when moving to a newer major.