The 100ms Penalty for a 1ms Query: How to Tame the Postgres JIT Compiler

You’ve spent hours indexing your tables, vacuuming like a pro, and trimming your SELECT statements down to the bare essentials. On paper, your query is a lightning-fast index seek that should take 1 millisecond, yet your monitoring tools are screaming about 100ms spikes in P99 latency.

Welcome to the world of the Postgres Just-In-Time (JIT) compiler—a feature that is technically impressive but frequently acts like an over-eager intern who spends three hours "optimizing" a task that takes five minutes to complete.

The "Helpful" Default that Bites

Since Postgres 12, JIT compilation (powered by LLVM) has been enabled by default. The logic is sound: if you have a massive, brain-melting analytical query involving complex expressions and millions of rows, JIT can compile those expressions into native machine code and shave 20% off the execution time.

But Postgres is an optimist. It uses a "cost-based" model to decide when to trigger JIT. If the estimated cost of your query exceeds jit_above_cost (defaulting to 100,000), Postgres starts the engines.

The problem? Cost estimates are often wrong, and the overhead of starting the LLVM compiler is massive compared to the execution time of a simple OLTP query.

Seeing the Invisible Wall

The first time I hit this, I thought the network was dropping packets. Then I ran an EXPLAIN ANALYZE on a query that felt sluggish despite having a perfect plan.

If you see something like this at the bottom of your plan, you've found the culprit:

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_id = 12345 AND status = 'shipped';

-- ... (Plan nodes here) ...
JIT:
  Functions: 4
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.542 ms, Inlining 23.410 ms, Optimization 45.122 ms, Emission 30.101 ms, Total 100.175 ms

Look at those numbers. The query itself might have taken 0.050 ms to fetch the row, but Postgres spent 100 ms deciding how to make that 0.050 ms faster. It’s the computational equivalent of spending $100 on gas to drive to the store to save $1 on milk.

Why Does This Happen?

JIT kicks in when the query planner *thinks* the query is going to be expensive. This usually happens in three scenarios:
1. Large Joins: Even if they end up being fast, the "potential" cost is high.
2. Complex Expressions: Lots of CASE statements or math in the WHERE clause.
3. Stale Statistics: If Postgres thinks a table has 10 million rows when it actually has 10, the estimated cost will trigger JIT unnecessarily.

The Quick Fix: The "Kill Switch"

If you are running a high-concurrency OLTP application (think: many small inserts, updates, and lookups), JIT is likely doing more harm than good. You can disable it globally in your postgresql.conf:

# postgresql.conf
jit = off

Or, if you aren't ready for such a commitment, try disabling it for your current session to see if the latency drops:

SET jit = off;
-- Run your query again
EXPLAIN ANALYZE SELECT ...

The Better Fix: Tuning the Thresholds

Maybe you actually *do* have some heavy reporting queries that benefit from JIT, but you want to stop it from bullying your small queries. Instead of a hard off, you can raise the barrier to entry.

The default jit_above_cost is 100,000. That’s actually quite low for modern hardware. Try bumping it up:

-- Tell Postgres: "Only JIT if the query is REALLY heavy"
ALTER SYSTEM SET jit_above_cost = 500000;
SELECT pg_reload_conf();

There are three main knobs you can turn:
* jit_above_cost: When to start using JIT at all.
* jit_inline_above_cost: When to try inlining functions (very expensive).
* jit_optimize_above_cost: When to apply expensive LLVM optimizations.

I’ve found that setting jit_inline_above_cost and jit_optimize_above_cost much higher than the base jit_above_cost prevents the most egregious latency spikes while still allowing some JIT benefits for the true whales.

Testing Your Changes

Don't just take my word for it. You can benchmark the impact directly in your database. I like to use a simple script to compare the "With JIT" vs "Without JIT" performance on a specific query:

-- Test 1: With JIT
SET jit = on;
EXPLAIN (ANALYZE, BUFFERS) SELECT SUM(price) FROM sales WHERE created_at > NOW() - INTERVAL '1 day';

-- Test 2: Without JIT
SET jit = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT SUM(price) FROM sales WHERE created_at > NOW() - INTERVAL '1 day';

In many cases, you’ll find that for any query finishing in under 500ms, disabling JIT actually results in a faster "wall clock" time because the compilation phase is skipped entirely.

Final Thoughts

JIT is a powerful tool, but it was designed with Data Warehousing (OLAP) in mind. If your database's primary job is serving a web app with thousands of tiny queries per second, the "JIT tax" will ruin your P99s.

My rule of thumb? Disable JIT by default for OLTP workloads. If you have specific, long-running batch jobs, enable it specifically for those sessions. Your CPU (and your on-call engineer) will thank you.