JIT Compilation Overhead on Short Queries #

A dashboard query runs in 22 ms from your laptop’s psql session and in 410 ms from the reporting service. The plans are identical node for node. The difference is at the bottom of the application’s plan: a JIT: section reporting 380 ms spent generating, inlining and optimizing machine code for a query that then ran for 30 ms.

JIT is one of the session-level inputs that can make the same SQL behave differently across connections, alongside the settings covered in session parameter drift.

The Cost-Model Condition #

PostgreSQL’s just-in-time compilation (enabled by default since version 12, when built with LLVM) compiles expression evaluation and tuple deforming into native code. The decision is made per query, from the plan’s total estimated cost:

Setting Default Effect when total cost exceeds it
jit on master switch
jit_above_cost 100000 compile expressions and deforming
jit_inline_above_cost 500000 also inline small functions — expensive
jit_optimize_above_cost 500000 also run LLVM optimization passes — expensive

Three properties make JIT a trap for short queries:

And one property makes it a drift problem: jit and its thresholds can be set by ALTER DATABASE, ALTER ROLE, connection options or SET, so two clients can disagree.

Cost thresholds switch on JIT stages A horizontal axis of estimated plan cost. Below 100,000 there is no JIT. Between 100,000 and 500,000, expressions and tuple deforming are compiled. Above 500,000, inlining and optimization passes are added, which dominate compile time. A marker shows a dashboard query with cost 612,000 that executes in 30 milliseconds but pays the full compile cost. 100,000 500,000 total cost → no JIT compile expressions + deform + inline + optimize (slow) dashboard query: cost 612,000, runs 30 ms the decision never looks at how long the query actually takes

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, SETTINGS)
SELECT date_trunc('hour', created_at) AS h, count(*)
FROM page_views
WHERE site_id = 42 AND created_at >= now() - interval '1 day'
GROUP BY 1;

From the reporting service’s role:

HashAggregate  (cost=611840.20..612102.40 rows=… width=16) (actual time=398.4..398.6 rows=24 loops=1)
  Group Key: date_trunc('hour', created_at)
  ->  Bitmap Heap Scan on page_views  (actual time=392.1..396.2 rows=18420 loops=1)
        …
Planning Time: 0.6 ms
JIT:
  Functions: 14
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 2.1 ms (Deform 0.6 ms), Inlining 71.3 ms, Optimization 204.8 ms, Emission 102.4 ms, Total 380.6 ms
  --  total cost 612,102 > 500,000 → inlining and optimization enabled
  --  Total 380.6 ms of JIT inside an execution of 409 ms
Execution Time: 409.2 ms

From psql as a different role:

HashAggregate  (cost=611840.20..612102.40 …) (actual time=21.6..21.8 rows=24 loops=1)
Settings: jit = 'off'                           -- set on this role long ago
Execution Time: 22.1 ms

Fields to read: the JIT: block’s Timing … Total against Execution Time, and Options to see which expensive stages ran. Note that JIT time appears inside actual time of the first nodes to execute compiled code, which is why the Bitmap Heap Scan above looks slow.

Reading the JIT block Three lines are annotated. Functions 14 is the number of compiled functions. Options showing inlining and optimization true means the expensive stages ran. Timing Total of 380 milliseconds is compared with execution time. Functions: 14 expressions compiled Options: Inlining true, Optimization true cost crossed 500,000 Timing: … Total 380.6 ms 93% of Execution Time compare Total with Execution Time before looking at any node

Step-by-Step Resolution #

  1. Look for JIT: in EXPLAIN ANALYZE output. If Total is a large share of Execution Time, JIT is the problem.

  2. Find where each side’s setting comes from.

    SELECT name, setting, source FROM pg_settings WHERE name LIKE 'jit%';
SELECT r.rolname, d.datname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase;

  3. Test the alternatives in a transaction:

    BEGIN;
SET LOCAL jit = off;
EXPLAIN (ANALYZE) <query>;
ROLLBACK;

BEGIN;
SET LOCAL jit_inline_above_cost = 5000000;
SET LOCAL jit_optimize_above_cost = 5000000;
EXPLAIN (ANALYZE) <query>;   -- keep cheap compilation, skip the expensive stages
ROLLBACK;

  4. Check whether the cost is inflated by an estimate error. If the plan’s cost is high only because of a misestimate, fixing it removes the JIT trigger and improves the plan — see spotting row estimate explosions.

  5. Pin the decision where the workload lives, not in each application:

    ALTER ROLE reporting_service SET jit = off;
-- or keep JIT for real analytics but avoid the expensive stages
ALTER DATABASE app SET jit_optimize_above_cost = 5000000;

  6. Verify from the application’s own connection with auto_explain or EXPLAIN (ANALYZE, SETTINGS) issued through the same role and pool.

Where the 409 milliseconds went A horizontal stacked bar for the reporting service run: 381 milliseconds of JIT generation, inlining, optimization and emission, and 28 milliseconds of actual query work. Below it, a short bar of 22 milliseconds for the same query with JIT off. jit = on JIT compile 381 ms jit = off 22 ms — the query work alone dark segment: executing the plan · light segment: compiling code for it

Before and After #

-- BEFORE: reporting_service role, jit on with default thresholds
JIT: Options: Inlining true, Optimization true … Total 380.6 ms       Execution Time: 409.2 ms

-- AFTER: ALTER ROLE reporting_service SET jit = off
(no JIT section)                                                        Execution Time: 22.4 ms

The query’s plan and cost are unchanged; only the decision to compile disappeared. Other roles in the same database — the nightly analytics jobs — keep JIT and its benefits for their long scans.

Common Pitfalls #

Testing from a session with different JIT settings. Local psql often runs as a role with JIT off or on a build without LLVM. Diagnostic signal: no JIT: block locally, present in production logs. Fix: test through the application’s role, or use EXPLAIN (SETTINGS) to compare.

Turning JIT off server-wide for everyone. Long analytical scans can lose real speedups. Diagnostic signal: large aggregations over hundreds of millions of rows slowing after the change. Fix: scope by role or database.

Blaming the first scan node. Compile time is charged to the nodes that first run compiled expressions. Diagnostic signal: a scan’s startup time roughly equal to the JIT total. Fix: read the JIT: block before investigating the scan.

Missing JIT under parallel plans. Each parallel worker compiles its own code, so JIT time multiplies with workers. Diagnostic signal: JIT: totals above wall-clock execution time in a Gather plan. Fix: raise thresholds for parallel-heavy workloads, as the worker count multiplies the overhead covered in parallel query execution.

Frequently Asked Questions #

What decides whether PostgreSQL uses JIT for a query? The plan’s total estimated cost. Above jit_above_cost expressions are compiled; above jit_inline_above_cost and jit_optimize_above_cost the expensive inlining and optimization stages are added. Actual runtime is not considered.

Why does JIT hurt short queries? Compilation happens at the start of every execution and can take hundreds of milliseconds, while the benefit is small per row. A query that is expensive on paper but fast in practice never recovers the cost.

Should I turn JIT off? For OLTP roles and databases, turning it off or raising the thresholds is common. Keep it for analytical workloads by scoping the change with ALTER ROLE or ALTER DATABASE.

Up: Session Parameter Drift