compute_query_id
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:
- the
query_idcolumn inpg_stat_statements - in
EXPLAIN VERBOSEoutput - in the PostgreSQL log, if
log_line_prefixcontains the%Qparameter
Note that the query ID value can be negative.
Change history
- PostgreSQL 14
- added (commit 5fd9dfa5)
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)
References
- PostgreSQL 14 documentation: compute_query_id