作者
digoal
日期
2021-04-08
标签
PostgreSQL , query id , 指纹 , compute_query_id
背景
PostgreSQL 14将pg_stat_statements插件的query id计算模块剥离到内核中, 使得内部可以直接使用query id功能.
默认query id不计算, 通过参数 compute_query_id 控制.
explain , log , 动态会话视图中增加query id的展示.
query id是什么? 例如多条sql支持某些输入的条件不一样, 其他部分都一样, 可以认为是同类sql, 那么通过query id来表达会比较方便.
注意指的不是绑定变量的sql.
1 /*-------------------------------------------------------------------------
2 *
3 * queryjumble.c
4 * Query normalization and fingerprinting.
5 *
6 * Normalization is a process whereby similar queries, typically differing only
7 * in their constants (though the exact rules are somewhat more subtle than
8 * that) are recognized as equivalent, and are tracked as a single entry. This
9 * is particularly useful for non-prepared queries.
10 *
11 * Normalization is implemented by fingerprinting queries, selectively
12 * serializing those fields of each query tree's nodes that are judged to be
13 * essential to the query. This is referred to as a query jumble. This is
14 * distinct from a regular serialization in that various extraneous
15 * information is ignored as irrelevant or not essential to the query, such
16 * as the collations of Vars and, most notably, the values of constants.
17 *
18 * This jumble is acquired at the end of parse analysis of each query, and
19 * a 64-bit hash of it is stored into the query's Query.queryId field.
20 * The server then copies this value around, making it available in plan
21 * tree(s) generated from the query. The executor can then use this value
22 * to blame query costs on the proper queryId.
23 *
24 * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
25 * Portions Copyright (c) 1994, Regents of the University of California
26 *
27 *
28 * IDENTIFICATION
29 * src/backend/utils/misc/queryjumble.c
30 *
31 *-------------------------------------------------------------------------
32 */
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5fd9dfa5f50e4906c35133a414ebec5b6d518493
```
Move pg_stat_statements query jumbling to core.
author Bruce Momjian bruce@momjian.us
Wed, 7 Apr 2021 17:06:47 +0000 (13:06 -0400)
committer Bruce Momjian bruce@momjian.us
Wed, 7 Apr 2021 17:06:56 +0000 (13:06 -0400)
commit 5fd9dfa5f50e4906c35133a414ebec5b6d518493
tree 4bf0c6c9088eb8b053b029b9be787939d48d2e3b tree
parent a282ee68a070a8adc6e6d45e8e643769c587ecc3 commit | diff
Move pg_stat_statements query jumbling to core.
Add compute_query_id GUC to control whether a query identifier should be
computed by the core (off by default). It's thefore now possible to
disable core queryid computation and use pg_stat_statements with a
different algorithm to compute the query identifier by using a
third-party module.
To ensure that a single source of query identifier can be used and is
well defined, modules that calculate a query identifier should throw an
error if compute_query_id specified to compute a query id and if a query
idenfitier was already calculated.
Discussion: https://postgr.es/m/20210407125726.tkvjdbw76hxnpwfi@nol
Author: Julien Rouhaud
Reviewed-by: Alvaro Herrera, Nitin Jadhav, Zhihong Yu
```
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4f0b0966c866ae9f0e15d7cc73ccf7ce4e1af84b
```
Make use of in-core query id added by commit 5fd9dfa5f5
author Bruce Momjian bruce@momjian.us
Wed, 7 Apr 2021 18:03:56 +0000 (14:03 -0400)
committer Bruce Momjian bruce@momjian.us
Wed, 7 Apr 2021 18:04:06 +0000 (14:04 -0400)
commit 4f0b0966c866ae9f0e15d7cc73ccf7ce4e1af84b
tree f0848c536dcce037e64218f52bd9bc8f1cc3f0ae tree
parent ec7ffb8096e8eb90f4c9230f7ba9487f0abe1a9f commit | diff
Make use of in-core query id added by commit 5fd9dfa5f5
Use the in-core query id computation for pg_stat_activity,
log_line_prefix, and EXPLAIN VERBOSE.
Similar to other fields in pg_stat_activity, only the queryid from the
top level statements are exposed, and if the backends status isn't
active then the queryid from the last executed statements is displayed.
Add a %Q placeholder to include the queryid in log_line_prefix, which
will also only expose top level statements.
For EXPLAIN VERBOSE, if a query identifier has been computed, either by
enabling compute_query_id or using a third-party module, display it.
Bump catalog version.
Discussion: https://postgr.es/m/20210407125726.tkvjdbw76hxnpwfi@nol
Author: Julien Rouhaud
Reviewed-by: Alvaro Herrera, Nitin Jadhav, Zhihong Yu
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
