Query fingerprint, and query_id

A query fingerprint is an identifier that collapses every execution of the same statement shape into one row of statistics. PostgreSQL computes it by hashing the parsed form of the statement after constants have been replaced by placeholders, so two queries differing only in their literal values share an id. The value appears as queryid in pg_stat_statements, as query_id in pg_stat_activity and in log lines, and it is the join key that connects what a statement costs to what it was doing at the time.

A hash of the tree, not of the text

The fingerprint is computed from the parse tree, which has consequences that surprise people who assume it is a hash of the text. Whitespace, comments and letter case do not change it. Neither does the value of a literal. But the object identifiers inside the tree do, so the same statement text against a table that was dropped and recreated fingerprints differently, and a statement issued in two schemas resolves to two ids.

The consequence that costs real work is that the fingerprint is not stable across major versions. The algorithm has changed more than once, including in 18 for constant lists and for same-named relations in different schemas, and nothing about the id announces which version produced it. A performance baseline keyed on queryid is therefore a baseline that silently empties on the upgrade, and the regression you most want to detect is the one the upgrade caused.

It is also not unique in the sense the word usually implies. It is a hash, collisions are possible, and the text stored beside it is the text of whichever execution first created the entry, so the stored text is an example rather than a definition.

Watching two statements become one row

Two executions differing only in a constant produce a single entry.

CREATE EXTENSION pg_stat_statements;
SELECT pg_stat_statements_reset();
SELECT count(*) FROM pg_class WHERE oid = 1259;
SELECT count(*) FROM pg_class WHERE oid = 2601;
SELECT queryid, calls, query FROM pg_stat_statements
 WHERE query LIKE 'SELECT count(*) FROM pg_class%';

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

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

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

       queryid        | calls |                    query                     
----------------------+-------+----------------------------------------------
 -4095396041832317534 |     2 | SELECT count(*) FROM pg_class WHERE oid = $1
(1 row)

Two statements, one row, two calls, and the literal replaced by $1. That id is from 14.24. The identical script on 18.6 returns a different number for the same statement, which is the upgrade problem above shown rather than asserted, and it is the reason a fingerprint should be treated as a key within one server rather than as a name for a query.

Computing the id at all is governed by compute_query_id, whose default setting turns it on when the statistics extension is loaded and leaves it off otherwise. A cluster where query_id is unexpectedly null usually has that setting at its default and no extension to trigger it.

Using it without being caught by it

Fingerprints are most useful where a plan changed under a stable statement, and the workflow for proving that is in query plan regression, including what to capture before an upgrade so the comparison is still possible afterwards. To join a fingerprint to what the session was waiting on rather than to what it cost, the sampling approach in active session history is the other half.