JIT Compilation, Thresholds, Analytical Workloads, and When JIT Hurts

Learning outcomes

After memory/I/O/parallel tuning, an analyst asks whether Just-in-Time (JIT) compilation should be “turned on for performance.” PostgreSQL JIT compiles parts of query execution—currently expression evaluation and tuple deforming—into native code using an available JIT provider such as LLVM. Compilation itself costs time, so short queries can become dramatically slower when JIT is forced.

1. Verify capability and thresholds

SELECT pg_jit_available() AS jit_available;SELECT name, setting, context, sourceFROM pg_settingsWHERE name IN (  'jit',  'jit_provider',  'jit_above_cost',  'jit_inline_above_cost',  'jit_optimize_above_cost',  'jit_expressions',  'jit_tuple_deforming')ORDER BY name;

If pg_jit_available() is false, the current server cannot perform JIT under its build/configuration. Do not turn the lesson into an LLVM installation requirement: JIT is optional and the mandatory learning outcome is understanding the decision/evidence model.

2. JIT is triggered by estimated plan cost at planning time

When jit=on, PostgreSQL compares the plan's total estimated cost with jit_above_cost. Higher thresholds separately control inlining and expensive optimization. This uses planner cost, not observed wall-clock duration.

EXPLAIN (COSTS ON, SETTINGS)SELECT sum((amount * amount) + sqrt((sort_key % 1000)::double precision))FROM app.ch22_perfWHERE category IN ('repair','inspection');

A query that runs slowly because it waits on a lock can still have low estimated CPU cost and gain nothing from JIT. JIT primarily targets long-running CPU-heavy expression/tuple-processing work.

3. Deliberately force JIT on a short query

BEGIN;SET LOCAL jit = on;SET LOCAL jit_above_cost = 0;SET LOCAL jit_inline_above_cost = -1;SET LOCAL jit_optimize_above_cost = -1;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY ON)SELECT sum(event_id)FROM app.ch22_perfWHERE event_id <= 1000;ROLLBACK;

Expected: EXPLAIN shows a JIT: section with generated functions and compilation timing. On such a short query, JIT generation/emission can be a large fraction of—or exceed—the execution work. PostgreSQL's own JIT documentation demonstrates this failure mode.

4. Compare the same short query with JIT off

BEGIN;SET LOCAL jit = off;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY ON)SELECT sum(event_id)FROM app.ch22_perfWHERE event_id <= 1000;ROLLBACK;

Run both variants several times and alternate order. Do not compare only one warmed query with one cold query. The key evidence is JIT compilation time relative to total execution, not one unrepeatable stopwatch value.

5. Heavier analytical expression: JIT might amortize

BEGIN;SET LOCAL max_parallel_workers_per_gather = 0;SET LOCAL jit = off;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY ON)SELECT category,       sum((amount * amount) + sqrt((sort_key % 1000)::double precision)),       avg((amount + sort_key)::double precision)FROM app.ch22_perfGROUP BY category;ROLLBACK;

BEGIN;SET LOCAL max_parallel_workers_per_gather = 0;SET LOCAL jit = on;SET LOCAL jit_above_cost = 0;SET LOCAL jit_inline_above_cost = 0;SET LOCAL jit_optimize_above_cost = 0;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY ON)SELECT category,       sum((amount * amount) + sqrt((sort_key % 1000)::double precision)),       avg((amount + sort_key)::double precision)FROM app.ch22_perfGROUP BY category;ROLLBACK;

Because the table is still a modest training dataset, JIT may or may not win on your hardware. That outcome is intentional. Record JIT's generation/inlining/optimization/emission time and compare it with the change in executor time. Scale data/expression work in a disposable environment if you want to locate the crossover point for your CPU/build.

6. Inlining and optimization increase compilation investment

jit_inline_above_cost and jit_optimize_above_cost apply additional compilation effort only after the query is JIT-eligible. Lowering all three thresholds together can turn a modest query into a compiler benchmark.

SHOW jit_above_cost;SHOW jit_inline_above_cost;SHOW jit_optimize_above_cost;

The thresholds are cost gates, not milliseconds. Calibrate them with representative analytical queries and concurrency, not a single query's reported duration.

7. Prepared generic plans freeze the JIT cost decision at plan time

For prepared statements that use a generic plan, PostgreSQL makes the JIT threshold decision when that generic plan is prepared. Changing JIT cost thresholds later at execution time does not retroactively change the generic plan's decision.

BEGIN;SET LOCAL plan_cache_mode = force_generic_plan;SET LOCAL jit = on;SET LOCAL jit_above_cost = 1000000;PREPARE ch22_jit(numeric) ASSELECT sum(amount * amount)FROM app.ch22_perfWHERE amount >= $1;EXPLAIN (ANALYZE, SETTINGS, TIMING OFF)EXECUTE ch22_jit(100);SET LOCAL jit_above_cost = 0;EXPLAIN (ANALYZE, SETTINGS, TIMING OFF)EXECUTE ch22_jit(100);DEALLOCATE ch22_jit;ROLLBACK;

If you need to test a new planning-time threshold, create/replan the statement under that threshold rather than assuming an already-selected generic plan will be reconsidered.

8. JIT and parallel query can interact

A production analytical query can simultaneously involve JIT, parallel workers, sorts/hashes and I/O. Isolate variables during experiments; otherwise a faster run cannot tell you whether JIT, worker count, cache state or spill avoidance caused the change.

9. Select JIT candidates from workload evidence, not query length

A long-running statement is not automatically a JIT candidate. JIT accelerates CPU-side expression evaluation and tuple deforming; it does not make a blocked lock disappear, increase storage throughput, or repair a cardinality error. Start with the Chapter 21 evidence chain: fingerprint cost, current waits, buffers/I/O, and a representative plan.

SELECT queryid, calls,       round(total_exec_time::numeric,1) AS total_exec_ms,       round(mean_exec_time::numeric,3) AS mean_exec_ms,       shared_blks_read, temp_blks_written, wal_bytes,       left(query,100) AS query_excerptFROM pg_stat_statementsWHERE dbid = (SELECT oid FROM pg_database WHERE datname=current_database())ORDER BY total_exec_time DESCLIMIT 15;

If the supplied pg_stat_statements extension is not enabled, skip this query and use application latency plus representative EXPLAIN evidence. High total/mean time with low lock-wait and a CPU-heavy plan is a better JIT candidate than a statement whose time is dominated by I/O, client backpressure, or blocking.

SELECT pid, application_name, state,       wait_event_type, wait_event, query_idFROM pg_stat_activityWHERE state = 'active'ORDER BY query_start;

An active backend with a lock or I/O wait is not spending that interval evaluating expressions. Conversely, a backend with no reported PostgreSQL wait still needs operating-system CPU evidence before you call it CPU-saturated. JIT tuning should follow that classification.

10. Build a repeatable JIT comparison record

For each candidate, store the same query inputs, row counts, planner settings, worker settings, cache/warmup procedure, JIT thresholds, plan shape, JIT compilation timing, and execution time. Run multiple alternating JIT-off/JIT-on samples. A change in one run is not a tuning result.

Only after the comparison is repeatable should you consider changing a role/database/global threshold. Often the better result is to leave global thresholds alone and selectively disable or enable JIT for a workload class.

Authoritative references

Performance settings are hardware-, concurrency-, plan-, operating-system-, and version-sensitive. These primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — JIT Overview
• PostgreSQL 18 — What Is JIT?
• PostgreSQL 18 — When to JIT?
• PostgreSQL 18 — JIT Configuration
• PostgreSQL 18 — System Information / pg_jit_available