PostgreSQL JIT compilation making short queries slow: measure the overhead and tune it per workload

#The goal

You have a query that should take 20 ms and sometimes takes 400 ms. CPU on the server is higher than the workload explains. Plans look good. The extra time is often JIT compilation: PostgreSQL turned the plan's expressions into machine code with LLVM before running it, and the compilation took far longer than the query.

JIT is on by default from PostgreSQL 12 when the server is built with LLVM support. The planner decides to JIT a query from its estimated total cost, not from how long the query will run. Any time cost estimates are inflated, JIT fires on queries that gain nothing from it. This guide shows how to prove it and how to switch it off only where it hurts.

#How the decision is made

Three cost thresholds control it. All compare against the plan's total estimated cost:

jit = off disables it completely. Setting a threshold to -1 disables that stage.

Typical situations where cost is high but runtime is short:

• Partitioned tables. A query on a table with hundreds of partitions can carry a large estimated cost before runtime pruning removes most partitions. JIT compiles code for every partition's scan.
• Row estimates that are too high. Stale statistics or correlated columns lead the planner to expect millions of rows and a few hundred are returned.
• Wide tables and many expressions. More columns and more CASE expressions mean more functions to compile.
• Parallel queries. Each parallel worker compiles its own copy, so JIT time is multiplied by the number of workers.
• Cached generic plans. The JIT decision is made at plan time. A generic plan built with pessimistic estimates keeps JIT on for every execution.

#Step 1: confirm JIT is available and on

SELECT pg_jit_available();
SHOW jit;
SHOW jit_provider;
SHOW jit_above_cost;
SHOW jit_inline_above_cost;
SHOW jit_optimize_above_cost;

If pg_jit_available() returns false, the LLVM package is missing or the build has no JIT support, and JIT is not your problem. On RHEL-family PGDG packages, JIT ships separately (for example postgresql17-llvmjit). Debian and Ubuntu packages usually include it in the server package. Package names vary by distribution and version.

#Step 2: see the cost in a single query

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, status, total
FROM   orders
WHERE  customer_id = 48213
AND    created_at >= now() - interval '30 days';

When JIT was used, the output ends with a block like this:

JIT:
  Functions: 142
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 9.8 ms, Inlining 61.2 ms, Optimization 205.4 ms, Emission 118.3 ms, Total 394.7 ms
Execution Time: 412.6 ms

Here roughly 395 ms of a 413 ms execution is compilation. The exact layout of the timing line varies by version: newer versions also break out deform time. Plain EXPLAIN without ANALYZE shows Functions and Options but not the timings.

Compare in the same session:

SET jit = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- same query
RESET jit;

If execution time drops from 400 ms to 20 ms, you have found it. Run both versions a few times. The first run also warms caches, which can make the comparison misleading.

For parallel plans, add VERBOSE to see JIT details per worker.

#Step 3: find every affected query

From PostgreSQL 15, pg_stat_statements records JIT work per statement:

SELECT  queryid,
        calls,
        round(mean_exec_time::numeric, 1)                          AS mean_ms,
        jit_functions,
        round((jit_generation_time + jit_inlining_time
             + jit_optimization_time + jit_emission_time)::numeric, 0) AS jit_ms,
        round(100 * (jit_generation_time + jit_inlining_time
             + jit_optimization_time + jit_emission_time)
             / nullif(total_exec_time, 0)::numeric, 1)             AS jit_pct_of_exec,
        left(query, 80)                                            AS query
FROM    pg_stat_statements
WHERE   jit_functions > 0
ORDER BY jit_ms DESC
LIMIT 25;

Read the result in two groups:

• High jit_pct_of_exec with many calls. These are OLTP queries paying for compilation every time. Fix these first.
• Low jit_pct_of_exec with long mean_ms. Analytical queries where JIT is a small share and may be helping. Leave them alone.

On PostgreSQL 12–14 there are no JIT columns. Use auto_explain with auto_explain.log_analyze = on on a sample of slow queries. The JIT block appears in the logged plan.

Tip

pg_stat_statements_reset() after a change gives you a clean comparison window. Note the time you reset so you can compare like with like.

#Step 4: fix the estimate first

If JIT fires because the planner thinks a query is expensive and it isn't, the bad estimate is the real problem. It is probably hurting join order and memory too.

-- Is the table's stats fresh?
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM   pg_stat_user_tables
WHERE  relname = 'orders';

ANALYZE orders;

-- Correlated columns: tell the planner they move together
CREATE STATISTICS orders_cust_created (dependencies, ndistinct)
  ON customer_id, created_at FROM orders;
ANALYZE orders;

For partitioned tables, check that plan-time pruning works: pass literal values or parameters the planner can use, and avoid functions on the partition key in the WHERE clause. Rerun Step 2 afterwards.

#Step 5: choose a scope for the change

Pick the narrowest scope that fixes the problem.

After ALTER SYSTEM, reload:

SELECT pg_reload_conf();

Role and database settings apply to new sessions. Connection poolers keep old sessions, so recycle the pool or wait for idle connections to close before you measure.

On managed services, set jit and the threshold parameters in the parameter group or server parameters instead of ALTER SYSTEM. Defaults differ between providers and versions, so check what your service ships before assuming JIT is on.

Warning

Turning JIT off instance-wide can slow down large analytical queries that do benefit from it, sometimes by a lot. If the same instance serves reports, use a role-level setting for the OLTP account instead.

#Step 6: verify

1. Rerun the pg_stat_statements query after a representative period. jit_functions should be 0 for the OLTP statements.
2. Compare mean_exec_time and p95 latency from your application metrics before and after.
3. Check that CPU dropped. JIT compilation is single-threaded per backend but adds up across many sessions.
4. Spot-check one large report with EXPLAIN (ANALYZE) to make sure it did not get slower.

#Prevent it next time

• Treat JIT as a workload setting, not a server default. Decide it per role when you create the service account.
• Add JIT to your query review. When a plan shows a JIT: block on an OLTP query, ask why the estimated cost is that high.
• Track jit_pct_of_exec from pg_stat_statements in your monitoring and alert when a frequently called statement crosses a threshold, for example 30%.
• Retest after upgrades and big data changes. A partition count that doubles or a table that grows tenfold can push queries over the threshold overnight.
• Keep statistics healthy: autovacuum analyze settings per large table and extended statistics where columns are correlated.