pg_qualstats: PostgreSQL 索引推荐

二月 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
--------+-------+--------+---------+------+--------+---------+--------+--------------+------------+------------------+------------+-----------------+------------+-------------------+---------+----------------+-----------
     101638416385298 │ <NULL> │  <NULL> │ <NULL> │       <NULL> │  115075651185864087711000009999929 │  <NULL> │ 'line 1'::text │ f
     101638416391298163852 │ <NULL> │       <NULL> │  497379130497379130100 │            <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 进行聚合。