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_id
column inpg_stat_statements
- in
EXPLAIN VERBOSE
output - in the PostgreSQL log, if
log_line_prefix
contains the%Q
parameter
Note that the query ID value can be negative.
Change history
- PostgreSQL 15
- option
regress
added (commit ebf6c524)
- option
- 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 | appdb | 5295917891061644709 | /* getAllLanguages_r */ + | | | SELECT * + | | | FROM language + | | | ORDER BY la 16402 | appdb | -6420466229898021482 | /* ObjectLib::_getObjectInfoForLink */ + | | | SELECT o.object_id, + | | | 16402 | appdb | 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 documentation: compute_query_id