JIT counters, and the extension version trap

An optimisation with no bill attached

Just-in-time compilation is on by default and has been for several majors. When the planner estimates that a query will cost more than a threshold, the server compiles parts of the expression evaluation into machine code rather than interpreting it, and for a long analytical query that walks millions of rows the saving is real.

For a query that was estimated expensive and turned out to be cheap, the compilation is pure overhead paid before the first row is produced. The classic version of this is a query against a partitioned table where the estimate is wrong, or a prepared statement whose generic plan looks costly, and the symptom is a statement whose execution time contains tens of milliseconds nobody can account for.

Until PostgreSQL 15 there was no per-statement accounting for it. You could see compilation time in a plan if you happened to be running the query by hand with the right options, and you could turn the whole feature off and see whether things improved, which is a blunt instrument to reach for on a production cluster. What you could not do was ask the statements extension which statements were paying and how much.

PostgreSQL 15 added the counters. The Additional Modules section of the PostgreSQL 15 release notes records the addition in one line.

The fixture

A table large enough that the planner will consider a scan of it expensive, and settings that force compilation regardless of the estimate so the demonstration is deterministic rather than lucky.

CREATE EXTENSION pg_stat_statements;
CREATE TABLE fact_amount (id bigint, amount numeric);
INSERT INTO fact_amount SELECT g, g::numeric / 7 FROM generate_series(1, 200000) AS g;
ANALYZE fact_amount;

CREATE EXTENSION
CREATE TABLE
INSERT 0 200000
ANALYZE

On 14, the question cannot be put:

SELECT jit_functions, jit_generation_time FROM pg_stat_statements LIMIT 1;

ERROR:  column "jit_functions" does not exist
LINE 1: SELECT jit_functions, jit_generation_time FROM pg_stat_state...
               ^

On 15, per statement

SET jit = on;
SET jit_above_cost = 0;
SET jit_inline_above_cost = 0;
SET jit_optimize_above_cost = 0;
SELECT pg_stat_statements_reset();
SELECT sum(amount), avg(amount) FROM fact_amount WHERE amount > 10;
SELECT jit_functions,
       round(jit_generation_time::numeric, 1) AS generation_ms,
       jit_emission_count
FROM pg_stat_statements WHERE query LIKE 'SELECT sum(amount)%';

SET
SET
SET
SET
 pg_stat_statements_reset 
--------------------------
 
(1 row)

             sum             |          avg           
-----------------------------+------------------------
 2857156787.8571428571428572 | 14290.7857142857142857
(1 row)

 jit_functions | generation_ms | jit_emission_count 
---------------+---------------+--------------------
             7 |           0.5 |                  1
(1 row)

Seven functions compiled for one aggregate over one table, with the generation time attached and an emission count recording how many times machine code was actually produced. That is the row a statement’s true cost has been missing.

The counters are cumulative over every execution of the statement, like everything else in that view, so the number worth computing is compilation time as a share of total execution time for the same row. A statement whose compilation time is a rounding error against its execution time is a statement where the feature is working. A statement where the two are comparable is a statement that should not be compiling at all.

There are four time counters rather than one, splitting generation from inlining, optimisation and emission, and they correspond to the four cost thresholds that gate each stage. That correspondence is what makes the counters actionable rather than merely interesting: if optimisation time dominates, the threshold governing optimisation is the one to raise, and the other stages keep working.

The part that is not about the server

Here is the trap, and it catches fleets that did everything else right. The statements extension is versioned independently of the server. A cluster that has been running it for years has whatever version was current when somebody first installed it, and upgrading the server does not change that.

DROP EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_statements VERSION '1.9';
SELECT extversion AS installed_version FROM pg_extension WHERE extname = 'pg_stat_statements';

DROP EXTENSION
CREATE EXTENSION
 installed_version 
-------------------
 1.9
(1 row)

That is a PostgreSQL 15 server. The binaries are 15, the shared library is 15, and the extension objects in this database are the older definition. Ask it for the new columns:

SELECT jit_functions FROM pg_stat_statements LIMIT 1;

ERROR:  column "jit_functions" does not exist
LINE 1: SELECT jit_functions FROM pg_stat_statements LIMIT 1;
               ^

The error is identical to the one 14 produced, on a server that has every capability the page describes. Somebody following an upgrade checklist, running the inventory query against the new cluster and finding the column missing, will reasonably conclude that the release note was wrong or that the feature landed later.

One statement fixes it:

ALTER EXTENSION pg_stat_statements UPDATE;
SELECT extversion AS installed_version FROM pg_extension WHERE extname = 'pg_stat_statements';
SELECT count(jit_functions) AS rows_with_jit_columns FROM pg_stat_statements;

ALTER EXTENSION
 installed_version 
-------------------
 1.10
(1 row)

 rows_with_jit_columns 
-----------------------
                     7
(1 row)

What to do with this on a real fleet

Three things, and the first is not about JIT at all.

• Add the extension update to the upgrade procedure, for every extension and every database, not just this one. An extension left at its old definition is a silent loss of whatever the new one added.
• Check the installed version before concluding a column is missing. The catalog answers it in one query and it takes less time than reading the release notes again.
• Once the columns are there, rank statements by compilation time as a share of total time rather than by compilation time alone. The absolute number is dominated by whatever runs most often.

The action that follows is usually not switching the feature off globally. It is raising the cost threshold that governs when compilation starts, so that only the queries genuinely large enough to benefit reach it, and the thresholds are session-settable, which means a single reporting job can opt in without the rest of the cluster changing.

What to do when compilation is the cost

Turning the feature off across a cluster is the blunt response and it is almost never the right one, because the queries it helps are helped a great deal. The useful lever is the set of cost thresholds that decide when each stage of compilation happens, and the counters are what tell you which one to move.

There are four stages and four thresholds, and they gate progressively more expensive work. The first decides whether to compile at all. The others decide whether to inline functions from the server’s own code, whether to run the optimiser over what was generated, and the emission of machine code itself. Each has its own counter and its own timer in the view, which means a statement’s row says not just that it compiled but how far down that sequence it went.

The common pattern on a badly served workload is that the later stages dominate. Generation is cheap; optimisation is not. A statement whose optimisation time is several times its generation time, on a plan that turns out to process very few rows, is a statement where raising the optimisation threshold alone fixes the problem and leaves the basic compilation in place.

The thresholds are compared against the planner’s estimated cost, which is the other half of the story and the reason this problem exists at all. A query whose estimate is wildly too high compiles as though it were enormous and then processes a handful of rows. So the second lever, and often the better one, is to fix the estimate: extended statistics on correlated columns, or a raised statistics target on the column the estimate is wrong about. That helps the plan as well as the compilation.

All of these are settable per session and per role, which makes them testable without a cluster-wide change. Set the threshold in one session, run the statement, compare the row.

One more thing is worth checking before touching a threshold at all, which is whether the server can compile in the first place. The provider that does the compilation is a separate build component, and a package that was assembled without it leaves the feature enabled in the settings and unable to do anything. On such a server every one of these counters stays at zero forever, and a team reading zeros will conclude the feature costs nothing rather than that it is absent. The counters are how you tell the two apart: a workload with large analytical queries and no compilation at all is a server where something is missing, not a server where the planner keeps deciding against it.

What the counters do not tell you

They do not tell you what the query would have cost without compilation. There is no counterfactual in the view, and a statement with high compilation time might still be faster than the same statement interpreted. The honest way to find out is to run the statement with the feature disabled in a session and compare, which is a two-minute experiment the counters tell you is worth doing on this statement rather than on the other several thousand.

They also do not attribute compilation to a plan. A statement whose plan changes keeps accumulating into the same row, so a query that started compiling last Tuesday because its estimates shifted shows as a gradual rise rather than as a step. The column PostgreSQL 17 added recording when a row started accumulating helps with the general version of that problem, and on 15 the practical answer is to reset the extension’s counters when you change anything and to know when you last did.

What it costs

The counters cost nothing beyond the extension you already run. They are written by the same code that already tracked execution time, they need no setting of their own, and they are present on every row whether or not the statement ever compiled anything.

The extension itself has its usual costs, which are a shared memory allocation sized by how many distinct statements you track and a small amount of work per execution. Neither changes because of this addition. The only new decision is the one at the end of an upgrade, and it is a single statement per database that nobody remembers to run.