compute_query_id moved into the server

Two views that could never be joined

Before PostgreSQL 14 the server had two answers to “what is expensive here” and no way to line them up. The activity view told you what each backend was running at this instant, as a string. The statements extension told you what had been expensive since the last reset, keyed by a number it computed itself. Matching a live session to its history meant comparing query text, and query text does not compare: the extension stores a normalised form with parameters replaced by placeholders, while the activity view stores exactly what the client sent, whitespace and literals and all.

People worked around it with prefix matching, or with regexp_replace chains that turned literals into placeholders and got it wrong on anything containing a string with a comma in it. The identifier existed all along inside the extension. It simply was not visible anywhere else.

PostgreSQL 14 moved the computation into the server. A setting decides whether the identifier is computed at all, and when it is, the number appears in the activity view as query_id, at the bottom of a verbose plan, and in every log line if the prefix asks for it. The extension stopped computing its own and started reading the server’s, which is what makes the two numbers the same number. The release notes record it under Monitoring, in one entry that also mentions the log prefix and verbose plans, which is easy to skim past because it reads like three small things rather than one join key.

The default is auto, and auto means the identifier is computed when something wants it. Loading the statements extension counts as wanting it. That is why most fleets already have this switched on without having decided to, and also why a cluster that does not load the extension has a query_id column full of nulls and an operator who assumes the feature is broken.

Asking a live session what it usually costs

The fixture is a small table of bookings and a statement slow enough to catch in the act. It runs once to leave a history row behind, then a second backend runs the identical text while this one watches.

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION dblink;
CREATE TABLE booking (id integer PRIMARY KEY, guest text, nights integer);
INSERT INTO booking SELECT g, 'guest-' || g, 1 + g % 5 FROM generate_series(1, 5000) AS g;

CREATE EXTENSION
CREATE EXTENSION
CREATE TABLE
INSERT 0 5000

SELECT count(*) FROM booking WHERE nights > 3 AND pg_sleep(0.0004) IS NOT NULL;
SELECT dblink_connect('probe', 'dbname=' || current_database() || ' user=postgres');
SELECT dblink_send_query('probe', 'SELECT count(*) FROM booking WHERE nights > 3 AND pg_sleep(0.0004) IS NOT NULL');
SELECT pg_sleep(1);
SELECT a.pid, a.state, a.query_id, s.calls, round(s.mean_exec_time::numeric, 1) AS mean_ms
FROM pg_stat_activity a
LEFT JOIN pg_stat_statements s ON s.queryid = a.query_id
WHERE a.pid <> pg_backend_pid() AND a.query_id IS NOT NULL AND a.state = 'active';
SELECT dblink_disconnect('probe');

 count 
-------
  2000
(1 row)

 dblink_connect 
----------------
 OK
(1 row)

 dblink_send_query 
-------------------
                 1
(1 row)

 pg_sleep 
----------
 
(1 row)

 pid | state  |       query_id       | calls | mean_ms 
-----+--------+----------------------+-------+---------
 130 | active | -8530781451332912452 |     1 |  4562.4
(1 row)

 dblink_disconnect 
-------------------
 OK
(1 row)

One row, and it answers a question the activity view alone cannot: the session is running something that has run before, and the previous run took about as long. That turns an incident question from “is this query stuck” into “this is what this query costs, and the surprise is that somebody is running it now”.

The same number is at the foot of a verbose plan, which is the other half of the loop. Having found an expensive identifier in the extension, you can confirm that the plan you are looking at belongs to it rather than to something that merely looks similar.

EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM booking WHERE nights > 3;

               QUERY PLAN               
----------------------------------------
 Aggregate
   Output: count(*)
   ->  Seq Scan on public.booking
         Output: id, guest, nights
         Filter: (booking.nights > 3)
 Query Identifier: -6192381539622155077
(6 rows)

Worth noticing before you rely on it: the EXPLAIN statement has its own identifier, distinct from the identifier of the statement inside it. If you put %Q in log_line_prefix and then go looking for a slow query by identifier in the log, the EXPLAIN you ran while investigating it is tagged with a different number and will not turn up in the same grep.

The part that does not survive the upgrade

The identifier is a hash of the parsed statement, and the way the server walks the parse tree to compute it is not frozen between majors. So the number changes. Here is the same statement text, keyed the way 14 keys it and the way 18 does.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pg_stat_statements_reset();

CREATE EXTENSION
 pg_stat_statements_reset 
--------------------------
 
(1 row)

SELECT count(*) FROM pg_class WHERE oid IN (1, 2);
SELECT count(*) FROM pg_class WHERE oid IN (1, 2, 3);
SELECT queryid, calls, query FROM pg_stat_statements WHERE query LIKE '%oid IN%' ORDER BY calls DESC;

On 14 those are two statements:

 count 
-------
     0
(1 row)

 count 
-------
     0
(1 row)

       queryid       | calls |                          query                          
---------------------+-------+---------------------------------------------------------
 6023020828663552257 |     1 | SELECT count(*) FROM pg_class WHERE oid IN ($1, $2)
 6382900881524441926 |     1 | SELECT count(*) FROM pg_class WHERE oid IN ($1, $2, $3)
(2 rows)

On 18 they are one:

 count 
-------
     0
(1 row)

 count 
-------
     0
(1 row)

       queryid        | calls |                           query                            
----------------------+-------+------------------------------------------------------------
 -6044873735315996275 |     2 | SELECT count(*) FROM pg_class WHERE oid IN ($1 /*, ... */)
(1 row)

Two things changed and only one of them is about the number. The first is that the value differs, which is unavoidable and merely means any baseline keyed on identifiers restarts at the upgrade. The second is that the grouping changed: from 18 a list of constants is collapsed regardless of its length, and the normalised text says so with a comment rather than by showing the placeholders. A workload that issues the same query with two, three and twenty values in an IN list was three hundred rows in the extension on 14 and is one row on 18.

That is an improvement for anyone reading the view by hand and a silent break for anything downstream that counts rows, computes a share of total time per identifier, or alerts on a query appearing for the first time. Nothing errors. The numbers simply stop being comparable across the boundary, and the shape of the change makes the new side look tidier, which is how it gets missed.

Two smaller adjustments in the same release are worth knowing if your fleet has several schemas: from 18 the identifier distinguishes same-named relations in different schemas, so two tenants running identical SQL against their own schemas no longer collapse into one row. On a multi-tenant cluster that is the opposite direction from the constant-list change, and the two together can move a row count either way.

The setting has four values and only two of them are obvious

on and off do what they say. auto is the default and means the server computes an identifier if something has asked for one, which in practice means the statements extension being loaded. The fourth value is regress, and it exists because the identifier is a hash: it changes between builds and between platforms, so a regression test whose expected output contains one would fail everywhere. With regress the identifier is still computed and still available through the views, and verbose plans simply stop printing it. If you find a cluster whose plans have no identifier line but whose activity view is full of them, that setting is why, and it is usually somebody’s test harness configuration that escaped.

The other thing the setting decides is who computes it. An extension can supply its own algorithm, which is the mechanism that existed before 14 and which still works: with the setting off, a module that computes an identifier is the only source, and with it on, the server’s is used and the module’s is ignored. That matters if your fleet runs a monitoring extension from a vendor, because the numbers the two produce are not the same numbers, and switching the setting on renames every statement in your history without touching a row.

One surprise worth having in advance: utility statements get identifiers too. A CREATE TABLE, a VACUUM and a CREATE EXTENSION each have one on this version, which means the statements extension accumulates rows for schema changes and maintenance alongside the queries. That is useful when you are trying to work out what a migration did, and it is noise in a report about application performance, and the boolean in the next section is not the column that separates them.

What to do with it rather than what to alert on

There is no threshold here. An identifier is a key, and the things worth alerting on are the metrics you key by it.

• Sample the activity view on a short interval and keep the identifier with each sample. That is the cheapest session history a cluster on this version can have, and it is what makes “what was the database doing at 03:10” answerable.
• Put %Q in the log prefix on anything you already log durations from. It costs nothing at write time and it turns the slow-query log into something joinable.
• Record the identifier alongside any plan you capture, so a plan that regresses can be matched to the statement whose cost moved.

Do not alert on an identifier disappearing. It disappears when the extension evicts it, when somebody resets the statistics, and when the server is upgraded, and none of those is the thing you meant.

What it costs to have on

The identifier is computed once per statement during parse analysis, on a tree the server has already built, and the result is cached for the life of the plan. Nothing measurable has ever been reported for it. The setting can be changed with a reload rather than a restart, so it is cheap to turn on and cheap to turn off again.

The real cost is elsewhere and it is worth saying plainly: auto is not on. A cluster with the setting at auto and nothing loaded that asks for the identifier computes nothing, and the column is null. If your collection layer depends on that column, set it to on explicitly rather than inheriting it from the presence of an extension somebody may remove.