compute_query_id

A server parameter enabling query identifiers
This entry relates to a PostgreSQL feature which is part of PostgreSQL 14, due to be released in late 2021.

compute_query_id is a configuration parameter enabling computation of a query identifier, which can be displayed via pg_stat_activity, in EXPLAIN output or in log output if configured in log_line_prefix.

compute_query_id was added in PostgreSQL 14.

Default value

The default value for compute_query_id is: auto.

Usage

In PostgreSQL 13 and earlier, the contrib module pg_stat_statements generated a query identifier for normalized queries, which enables tracking of essentially identical queries even if there are variations in query values and/or query format. From PostgreSQL 14 this value is computed in core PostgreSQL, making it available in other contexts; specifically:

Note that the query ID value can be negative.

Change history

Examples

Query ID displayed in pg_stat_activity:

postgres=# SELECT datid, datname, query_id, substr(query,1,64) AS query
             FROM pg_stat_activity WHERE datid IS NOT NULL;
 datid | datname  |       query_id       |                              query                               
-------+----------+----------------------+------------------------------------------------------------------
 16403 | postgres |  8528657101864406127 | SELECT datid, datname, query_id, substr(query,1,64) AS query FRO
 16402 | epp      |  5295917891061644709 | /* getAllLanguages_r */                                         +
       |          |                      |   SELECT *                                                      +
       |          |                      |     FROM language                                               +
       |          |                      | ORDER BY la
 16402 | epp      | -6420466229898021482 | /* ObjectLib::_getObjectInfoForLink */                          +
       |          |                      |     SELECT o.object_id,                                         +
       |          |                      |  
 16402 | epp      |  5295917891061644709 | /* getAllLanguages_r */                                         +
       |          |                      |   SELECT *                                                      +
       |          |                      |     FROM language                                               +
       |          |                      | ORDER BY la
(4 rows)

Query ID displayed in the log file (with log_line_prefix set to '%m [%p; %Q]'):

2021-07-21 09:46:50.810 UTC [2658840; -2049453941623996126] LOG:  duration: 0.067 ms
2021-07-21 09:46:50.810 UTC [2658840; 3422818749220588372] LOG:  duration: 0.056 ms
2021-07-21 09:46:50.810 UTC [2658840; -2269355413466007673] LOG:  duration: 0.103 ms
2021-07-21 09:46:50.811 UTC [2658840; -2049453941623996126] LOG:  duration: 0.063 ms
2021-07-21 09:46:50.811 UTC [2658840; 3422818749220588372] LOG:  duration: 0.059 ms
2021-07-21 09:46:50.811 UTC [2658840; 1878833853774024825] LOG:  duration: 0.107 ms
2021-07-21 09:46:50.811 UTC [2658840; -2049453941623996126] LOG:  duration: 0.061 ms
2021-07-22 09:46:50.812 UTC [2658840; 5295917891061644709] LOG:  duration: 0.154 ms

If pg_stat_statements is installed, the query ID can be matched to an entry in the pg_stat_statements view:

postgres=# SELECT userid, dbid, query FROM pg_stat_statements WHERE queryid=-2269355413466007673;
 userid | dbid  |          query           
--------+-------+--------------------------
  16384 | 16402 | /* getSessionMessage */ +
        |       |     SELECT warning      +
        |       |       FROM user_session +
        |       |      WHERE session_id=$1
(1 row)

EXPLAIN VERBOSE will also emit the query ID:

appdb=> EXPLAIN VERBOSE
          SELECT warning FROM user_session 
           WHERE session_id='2c7fe5d06fd9d6c0cc08883355c962b1';
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Index Scan using user_session_pkey on epp.user_session  (cost=0.28..8.29 rows=1 width=32)
   Output: warning
   Index Cond: ((user_session.session_id)::text = '2c7fe5d06fd9d6c0cc08883355c962b1'::text)
 Query Identifier: -2269355413466007673
(4 rows)

Categories

GUC configuration item

See also

pg_stat_activity, EXPLAIN, pg_stat_statements