Why the Same Postgres Query Can Randomly Get Slower With No Code Change
Imagine you're the engineer on call when a dashboard query that normally runs in a few hundred milliseconds suddenly takes several seconds. Nothing in the query changed. No new indexes were dropped. CPU usage on the box looks higher than before, not lower. EXPLAIN ANALYZE shows the right index and the right join order. So what changed?
The answer is in a part of the plan that's easy to skip past: the JIT block.
One version note up front: starting with Postgres 19, JIT is off by default. The project found the cost based activation logic unreliable, since a small shift in a table's estimated cost, from something as unrelated as a few extra rows, could push a query across the threshold and trigger JIT with no warning. That instability is the whole reason this trade-off matters. On Postgres 18 or earlier, JIT is on by default and this can happen to you right now.
Root Cause
Since Postgres 11, the planner can compile parts of a query into native machine code at runtime using LLVM, instead of always interpreting expressions row by row through a generic tree-walking evaluator. This is JIT compilation.
JIT usually does make expensive, CPU-bound queries faster. But compiling machine code costs planning time. For a query that scans a few hundred rows, that compilation overhead can exceed the entire execution time. Short queries pay a fixed tax with nothing to show for it. The scenario above is what happens when a query's estimated cost drifts, for any reason, across the JIT threshold, and it starts paying that tax on every run.
What's Happening Under the Hood
Without JIT, Postgres evaluates expressions by walking an expression tree at runtime. Every WHERE clause, arithmetic operation, and type coercion goes through a generic interpreter function that dispatches on node type per row. The dispatch cost is small per call but repeats for every row and every expression node.
With JIT, once a query clears the cost threshold, Postgres uses LLVM to emit native code specific to that query's expressions, tuple deforming, and aggregate transitions. This removes the per-node dispatch overhead for that execution, which is where the speedup on large scans and aggregates comes from.
The cost is the compilation step itself: building LLVM IR, running optimization passes (if jit_optimize_above_cost is cleared), emitting native code, and loading it into the backend process. This happens once per query execution, not once per row, so it only pays off when the row count is high enough for per-row savings to outweigh the fixed compilation cost.
How Postgres Decides When to JIT
On versions where JIT is enabled, Postgres compares a query's estimated total cost (the number at the top of EXPLAIN) against three thresholds:
jit_above_cost (default 100000): cost above which JIT kicks in at all
jit_optimize_above_cost (default 500000): cost above which expensive optimization passes run
jit_inline_above_cost (default 500000): cost above which small functions and operators get inlined
If the estimated cost is below jit_above_cost, JIT never runs, regardless of actual execution behavior. Estimates come from planner statistics, not reality. A query the planner thinks is cheap but is actually a slow CPU-heavy aggregate won't get JIT. A query the planner thinks is expensive but is served instantly from cache will pay the JIT tax for nothing.
Tuning by Workload Shape
OLTP workloads (many short queries, point lookups, simple joins) generally do better with JIT disabled, or with jit_above_cost raised well above default so it only fires on rare expensive outliers.
Analytical workloads (large aggregations, complex expressions, big joins over millions of rows) are what JIT was built for. Defaults or lower thresholds tend to work fine here.
Check the JIT block in EXPLAIN ANALYZE directly:
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(amount)
FROM orders
WHERE created_at > now() - interval '90 days'
GROUP BY customer_id;
JIT:
Functions: 8
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 2.1 ms, Inlining 4.3 ms, Optimization 18.7 ms, Emission 6.2 ms, Total 31.3 ms
That Total line is the compilation tax. Compare it to actual execution time. If it's a meaningful fraction and the query runs frequently, raise the thresholds or disable JIT for that workload.
To isolate JIT as a variable directly:
SET jit = off;
EXPLAIN ANALYZE SELECT * FROM big_table WHERE status = 'active';
SET jit = on;
SET jit_above_cost = 10; -- force JIT to trigger for comparison
EXPLAIN ANALYZE SELECT * FROM big_table WHERE status = 'active';
Running the same query both ways shows directly whether JIT helps or hurts for that query shape.
Failure Scenarios to Watch For
Connection poolers with short-lived queries. High query volume through something like PgBouncer in transaction mode means per-query JIT overhead adds up across the fleet, even if no single query looks slow.
Statistics drift. Stale statistics after a bulk load can cause the planner to badly over or underestimate cost, making JIT fire when it shouldn't or skip when it should. Run ANALYZE after large data changes.
Parameterized queries with variable row counts. A prepared statement run once with a highly selective parameter and once with a broad one can get very different cost estimates, so JIT triggers inconsistently for the "same" query.
References
Final Thought
JIT compilation isn't a bug and it isn't free performance. It's a trade-off the planner makes based on cost estimates that can be wrong. Read the JIT block in EXPLAIN ANALYZE on your actual workload before assuming the defaults are right for you.
Have you had JIT quietly tank a query, or save one? What did your jit_above_cost end up tuned to?
#postgresql #databaseperformance #backenddevelopment #queryoptimization
