compute_query_id

• 19
• 18
• 17
• 16
• 15
• 14

查询 ID 显示在 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)

查询 ID 显示在日志文件中（当 log_line_prefix 设置为 '%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

如果安装了 pg_stat_statements，查询 ID 可以与 pg_stat_statements 视图中的条目匹配

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 也会输出查询 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)

• PostgreSQL 文档： compute_query_id