二月 4, 2024
摘要：pg_qualstats扩展收集条件谓词相关的统计信息，帮助查找缺少哪些索引。
目录
介绍
pg_qualstats 是一个 PostgreSQL 扩展，用于保存WHERE语句和JOIN子句中条件谓词的统计信息。
该扩展的目标是让 DBA 可以回答一些特定的问题，这些问题的答案很难获得：
- 有哪些查询使用到了这个列？
- 这个 where 子句最常使用的值是什么？
- 如果使用某些值而不是另一些值，我在返回的行数分布中是否有一些明显的偏差？
- 在一个 WHERE 子句中，有哪些列会经常一起使用？
如果您希望能够分析数据库中最常执行的限定词（谓词），这将非常有用。powa 项目会利用这一点来提供高级索引建议。
它还允许您确定那些经常一起查询的列，来识别相关列。
该扩展是通过在查询中查找已知的模式来工作的。目前，这包括：
- 二元运算符 OpExpr，其中至少有一边是表里的列。只要有可能，就会交换谓词，以便将
CONST OP VAR表达式转换为VAR COMMUTED_OP CONST。AND 和 OR 表达式成员计为单独的条目。例如：WHERE column1 = 2，WHERE column1 = column2，WHERE 3 = column3。 - ScalarArrayOpExpr，其中左侧是一个 VAR，右侧是一个数组常量。对于数组中的每个元素，都会计算一次表达式。例如：WHERE column1 IN (2, 3) 将视为 (column1, ‘=’) 运算符对出现了 2 次。
- BooleanTest，其中表达式是一个简单的布尔列引用。例如：WHERE column1 IS TRUE。请注意，像 WHERE column1、WHERE NOT column1 这样的子句还不会被 pg_qualstats 处理。
该扩展还会根据每个不同的查询 ID，按原样保存第一次出现的查询文本，但会限制最多 pg_qualstats.max 个条目。
请注意，在重新启动 PostgreSQL 服务器时，收集的数据不会保存。
安装
- 与 PostgreSQL 9.4 或更高版本兼容
- 需要 postgresql 头文件
- sudo make install
- 将 pg_qualstats 添加到
shared_preload_libraries设置：
shared_preload_libraries = 'pg_qualstats'
配置
可以在 postgresql.conf 中配置以下 GUC 参数：
- pg_qualstats.enabled（布尔型，默认为 true）：是否应启用 pg_qualstats。
- pg_qualstats.track_constants（布尔型，默认为 true）：pg_qualstats 是否应该单独跟踪每个常量值。禁用该 GUC 参数可以大大减少跟踪谓词所需的条目数。
- pg_qualstats.max：跟踪的谓词文本和查询文本的最大数量（默认为 1000）。
- pg_qualstats.resolve_oids（布尔型，默认值为 false）：pg_qualstats 是否应该在查询时解析 oid，还是只存储 oid。启用该参数让数据分析变得更加容易，因为不需要连接到执行查询的数据库，但它会占用更多空间（启用时每个条目 624 个字节，关闭时 176 个字节）。此外，这将需要进行一些系统表查找，这些查找不是免费的。
- pg_qualstats.track_pg_catalog（布尔型，默认为 false）：pg_qualstats 是否应该对 pg_catalog 模式中的对象收集谓词。
- pg_qualstats.sample_rate（double，默认 -1）：查询的采样百分比。例如，0.1 表示只会对 10 个查询中的 1 个进行采样。默认值（-1）表示自动，计算出的值为 1 / max_connections，因此从统计学上讲，很少会出现并发问题。
更新扩展
请注意，由于所有扩展都是在shared_preload_libraries中配置的，因此仅在使用新的共享库版本重新启动 PostgreSQL 后，大多数更改才会应用生效。扩展对象本身仅提供用于访问内部数据结构的 SQL 包装器。
从版本 2.0.4 开始，提供了一个升级脚本，仅允许从前一个版本升级。如果要跨多个版本或从早于 2.0.3 的版本升级扩展，则需要删除和重新创建扩展，以使用最新版本。
用法
在任何数据库中创建扩展：
CREATE EXTENSION pg_qualstats;
函数
该扩展定义了以下函数：
-
pg_qualstats：返回每个限定符的计数，由表达式哈希值标识。该哈希值标识每个表达式。
- userid：执行查询的用户的 ID。
- dbid：执行查询所在的数据库的 OID。
- lrelid, lattnum：左侧 VAR 对应的关系和属性的编号 oid（如果有）。
- opno：表达式中使用的运算符的 oid。
- rrelid, rattnum：右侧 VAR 对应的关系和属性的编号 oid（如果有）。
- qualid：所属 “AND” 表达式的规范化标识符（如果有）。该标识符的计算不包括常量。这对于识别一起使用的谓词很有用。
- uniquequalid：所属 “AND” 表达式的唯一标识符（如果有）。该标识符的计算包括常量。
- qualnodeid：此简单谓词的规范化标识符。该标识符的计算不包括常量。
- uniquequalnodeid：此简单谓词的唯一标识符。该标识符的计算包括常量。
- occurences：调用此谓词的次数，即相关查询执行的次数。
- execution_count：此谓词被执行的次数，即它处理的行数。
- nbfiltered：此谓词过滤掉的元组数。
- constant_position：解析器报告的原始查询字符串中常量的位置。
- queryid：如果安装了 pg_stats_statements，则标识此查询的 queryid，否则为 NULL。
- constvalue：右侧常量（如果有）的字符串表示形式，截断为 80 个字节。这需要是超级用户或 pg_read_all_stats 的成员（从 PostgreSQL 10 开始），否则将显示 “
"。 - eval_type：计算类型。‘f’ 表示扫描后计算的谓词，或者 ‘i’ 表示索引谓词。
示例：
ro=# select * from pg_qualstats;
userid │ dbid │ lrelid │ lattnum │ opno │ rrelid │ rattnum │ qualid │ uniquequalid │ qualnodeid │ uniquequalnodeid │ occurences │ execution_count │ nbfiltered │ constant_position │ queryid │ constvalue │ eval_type
--------+-------+--------+---------+------+--------+---------+--------+--------------+------------+------------------+------------+-----------------+------------+-------------------+---------+----------------+-----------
10 │ 16384 │ 16385 │ 2 │ 98 │ <NULL> │ <NULL> │ <NULL> │ <NULL> │ 115075651 │ 1858640877 │ 1 │ 100000 │ 99999 │ 29 │ <NULL> │ 'line 1'::text │ f
10 │ 16384 │ 16391 │ 2 │ 98 │ 16385 │ 2 │ <NULL> │ <NULL> │ 497379130 │ 497379130 │ 1 │ 0 │ 0 │ <NULL> │ <NULL> │ │ f
-
pg_qualstats_index_advisor(min_filter, min_selectivity, forbidden_am): 执行一次全局索引推荐。默认情况下，仅考虑过滤至少 1000 行和平均 30% 行数的谓词，但这个可以作为参数传递。如果想排除一些索引访问方法，也可以提供一个包含索引访问方法的数组。例如，在 PostgreSQL 9.6 及更早版本上，
hash索引会被忽略，因为这些索引还不是崩溃安全的。示例：
SELECT v
FROM json_array_elements(
pg_qualstats_index_advisor(min_filter => 50)->'indexes') v
ORDER BY v::text COLLATE "C";
v
---------------------------------------------------------------
"CREATE INDEX ON public.adv USING btree (id1)"
"CREATE INDEX ON public.adv USING btree (val, id1, id2, id3)"
"CREATE INDEX ON public.pgqs USING btree (id)"
(3 rows)
SELECT v
FROM json_array_elements(
pg_qualstats_index_advisor(min_filter => 50)->'unoptimised') v
ORDER BY v::text COLLATE "C";
v
-----------------
"adv.val ~~* ?"
(1 row)
- pg_qualstats_deparse_qual：将存储的谓词格式化为
tablename.columname operatorname ?。这主要是针对全局索引推荐的。 - pg_qualstats_get_idx_col：对于给定的谓词，检索引用的列名和所有可能的运算符类。这主要是针对全局索引推荐的。
- pg_qualstats_get_qualnode_rel：对于给定的谓词，返回引用的完全限定的表名。这主要是针对全局索引推荐的。
- pg_qualstats_example_queries：返回所有存储的查询文本。
- pg_qualstats_example_query：返回指定 queryid 的存储的查询文本（如果有），否则为 NULL。
- pg_qualstats_names：返回所有存储的查询文本。
- pg_qualstats_reset：重置内部计数器，丢弃遇到的每个条件谓词。
视图
除此之外，该扩展还在 pg_qualstats 的函数之上定义了一些视图：
-
pg_qualstats：对 pg_qualstats() 的调用结果，过滤出当前数据库相关的信息。
-
pg_qualstats_pretty：执行适当的连接，对 pg_qualstats 视图中每个属性，显示易读的聚合的形式。
示例：
ro=# select * from pg_qualstats_pretty;
left_schema | left_table | left_column | operator | right_schema | right_table | right_column | occurences | execution_count | nbfiltered
-------------+------------------+-------------+--------------+--------------+-------------+--------------+------------+-----------------+------------
public | pgbench_accounts | aid | pg_catalog.= | | | | 5 | 5000000 | 4999995
public | pgbench_tellers | tid | pg_catalog.= | | | | 10 | 10000000 | 9999990
public | pgbench_branches | bid | pg_catalog.= | | | | 10 | 2000000 | 1999990
public | t1 | id | pg_catalog.= | public | t2 | id_t1 | 1 | 10000 | 9999
-
pg_qualstats_all：对每个属性/运算符对的计数求和，无论其作为操作数的位置如何（左边或右边），将 AND 子句中使用的属性组合在一起。
示例：
ro=# select * from pg_qualstats_all;
dbid | relid | userid | queryid | attnums | opno | qualid | occurences | execution_count | nbfiltered | qualnodeid
-------+-------+--------+---------+---------+------+--------+------------+-----------------+------------+------------
16384 | 16385 | 10 | | {2} | 98 | | 1 | 100000 | 99999 | 115075651
16384 | 16391 | 10 | | {2} | 98 | | 2 | 0 | 0 | 497379130
- pg_qualstats_by_query：仅返回
VAR OPERATOR CONSTANT形式的谓词，按 queryid 进行聚合。