Redrock Postgres 搜索 英文
版本: 9.3 / 9.4 / 9.5 / 9.6 / 10 / 11 / 12 / 13 / 14

14.2. 规划器使用的统计信息

14.2.1. 单列统计
14.2.2. 扩展统计

14.2.1. 单列统计

如我们在上一节所见,查询规划器需要估计一个查询要检索的行数,这样才能对查询计划做出好的选择。 本节对系统用于这些估计的统计信息进行一个快速的介绍。

统计信息的一个部分就是每个表和索引中的项的总数,以及每个表和索引占用的磁盘块数。这些信息保存在pg_class表的reltuplesrelpages列中。 我们可以用类似下面的查询查看这些信息:

SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'tenk1%';

       relname        | relkind | reltuples | relpages
----------------------+---------+-----------+----------
 tenk1                | r       |     10000 |      358
 tenk1_hundred        | i       |     10000 |       30
 tenk1_thous_tenthous | i       |     10000 |       30
 tenk1_unique1        | i       |     10000 |       30
 tenk1_unique2        | i       |     10000 |       30
(5 rows)

这里我们可以看到tenk1包含 10000 行, 它的索引也有这么多行,但是索引远比表小得多(不奇怪)。

出于效率考虑,reltuplesrelpages不是实时更新的 ,因此它们通常包含有些过时的值。它们被VACUUMANALYZE和几个 DDL 命令(例如CREATE INDEX)更新。一个不扫描全表的VACUUMANALYZE操作(常见情况)将以它扫描的部分为基础增量更新reltuples计数,这就导致了一个近似值。在任何情况中,规划器将缩放它在pg_class中找到的值来匹配当前的物理表尺寸,这样得到一个较紧的近似。

大多数查询只是检索表中行的一部分,因为它们有限制要被检查的行的WHERE子句。 因此规划器需要估算WHERE子句的选择度,即符合WHERE子句中每个条件的行的比例。 用于这个任务的信息存储在pg_statistic系统目录中。 在pg_statistic中的项由ANALYZEVACUUM ANALYZE命令更新, 并且总是近似值(即使刚刚更新完)。

除了直接查看pg_statistic之外, 手工检查统计信息的时候最好查看它的视图pg_statspg_stats被设计为更容易阅读。 而且,pg_stats是所有人都可以读取的,而pg_statistic只能由超级用户读取(这样可以避免非授权用户从统计信息中获取一些其他人的表的内容的信息。pg_stats视图被限制为只显示当前用户可读的表)。例如,我们可以:

SELECT attname, inherited, n_distinct,
       array_to_string(most_common_vals, E'\n') as most_common_vals
FROM pg_stats
WHERE tablename = 'road';

 attname | inherited | n_distinct |          most_common_vals
---------+-----------+------------+------------------------------------
 name    | f         |  -0.363388 | I- 580                        Ramp+
         |           |            | I- 880                        Ramp+
         |           |            | Sp Railroad                       +
         |           |            | I- 580                            +
         |           |            | I- 680                        Ramp
 name    | t         |  -0.284859 | I- 880                        Ramp+
         |           |            | I- 580                        Ramp+
         |           |            | I- 680                        Ramp+
         |           |            | I- 580                            +
         |           |            | State Hwy 13                  Ramp
(2 rows)

注意,这两行显示的是相同的列,一个对应开始于road表(inherited=t)的完全继承层次, 另一个只包括road表本身(inherited=f)。

ANALYZEpg_statistic中存储的信息量(特别是每个列的most_common_vals中的最大项数和histogram_bounds数组)可以用ALTER TABLE SET STATISTICS命令为每一列设置, 或者通过设置配置变量default_statistics_target进行全局设置。 目前的默认限制是 100 个项。提升该限制可能会让规划器做出更准确的估计(特别是对那些有不规则数据分布的列), 其代价是在pg_statistic中消耗了更多空间,并且需要略微多一些的时间来计算估计数值。 相比之下,比较低的限制可能更适合那些数据分布比较简单的列。

更多规划器对统计信息的使用可参阅第 68 章

14.2.2. 扩展统计

通常会看到缓慢的查询运行错误的执行计划,因为查询子句中使用的多列是相关的。 规划器通常假定多个条件彼此独立,当列值相关时,这种假设不成立。 由于每个单独列的性质,定期统计无法捕捉有关跨列关联的任何知识。 但是,PostgreSQL能够计算多元统计信息, 它可以捕获这些信息。

由于可能的列组合数量非常大,因此自动计算多元统计信息是不切实际的。 相反,可以创建扩展统计信息对象, 通常称为统计信息对象,以指示服务器通过有趣的列集获得统计信息。

统计信息对象是使用CREATE STATISTICS创建的, 它可以查看更多详细信息。创建这样一个对象只是创建一个表示对统计信息感兴趣的目录条目。 实际的数据收集由ANALYZE执行(手动命令或后端自动分析)。 可以在pg_statistic_ext 目录中检查收集的值。

ANALYZE根据计算常规单列统计信息所用的相同表行样本来计算扩展统计信息。 由于通过增加表或其任何列的统计目标(如前一节中所述)来增加样本大小, 因此较大的统计目标通常会导致更加准确的扩展统计量,以及花费更多时间计算它们。

以下小节介绍当前支持的扩展统计信息的种类。

14.2.2.1. 函数依赖

最简单的扩展统计信息跟踪函数依赖,一个用于数据库标准表单的定义中的概念。 如果知道a的值足够确定b的值, 我们说列b函数依赖于列a, 即没有两行会有相同的a值但b值不同。 在完全规范化的数据库中,函数依赖关系只应存在于主键和超级键上。 但是,实际上很多数据集由于各种原因未完全标准化; 出于性能原因的故意的非规范化是一个常见的例子。即使在完全标准化的数据库中, 某些列之间也可能存在部分相关性,可以将其表示为部分函数依赖性。

函数依赖的存在直接影响某些查询中估计的准确性。 如果查询在独立列和从属列上都包含条件,则从属列上的条件不会进一步减小结果大小; 但没有关于函数依赖关系的知识,查询规划器会认为条件是独立的,导致低估结果大小。

为了向规划器通知函数依赖关系,ANALYZE可以收集跨列依赖关系的度量。 评估所有列集之间的依赖程度会非常昂贵,因此数据收集仅限于那些在使用 dependencies选项定义的统计信息对象中一起出现的列组。 建议仅为强关联的列组创建dependencies统计信息, 以避免在ANALYZE和以后的查询计划中产生不必要的开销。

这里是一个收集函数依赖性统计信息的示例:

CREATE STATISTICS stts (dependencies) ON zip, city FROM zipcodes;

ANALYZE zipcodes;

SELECT stxname, stxkeys, stxdependencies
  FROM pg_statistic_ext
  WHERE stxname = 'stts';
 stxname | stxkeys |             stxdependencies               
---------+---------+------------------------------------------
 stts    | 1 5     | {"1 => 5": 1.000000, "5 => 1": 0.423130}
(1 row)

在这里可以看出,第1列(zip code)完全确定第5列(city),因此系数为1.0, 而city仅确定大约42%的zip code,这意味着有很多cities(58%)是由多个ZIP code代表的。

当计算包含函数依赖性列的查询的选择性时,规划器使用依赖系数调整每个条件的选择性估计, 以免产生低估。

14.2.2.1.1. 函数依赖的局限性

函数依赖性当前仅在考虑将列与常量值进行比较的简单相等条件时才适用。 它们不用于改进对比较两列或将列与表达式进行比较的相等条件的估计值, 也不用于范围子句、LIKE或任何其他类型的条件。

When estimating with functional dependencies, the planner assumes that conditions on the involved columns are compatible and hence redundant. If they are incompatible, the correct estimate would be zero rows, but that possibility is not considered. For example, given a query like 当使用函数依赖性进行估计时,规划器假设包含列上的条件是兼容的,并且因此使冗余的。 如果它们不兼容,则正确的估计应该是0行,但是不考虑这种可能性。例如, 给出一个查询

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';

规划器将忽略city子句没有改变选择性,这是正确的。不过, 它会对下面语句做出相同的假设

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';

即使实际上没有行满足这个查询。然而,函数依赖统计信息没有提供足够的信息来得出结论。

在许多实际情况中,这个假设通常是满足的;例如,应用程序中可能存在GUI, 只允许选择兼容的city和ZIP code值用于查询。但是,如果情况并非如此, 函数依赖可能不是一个可行的选择。

14.2.2.2.  N 个不同多变量的计数

单列统计信息存储每列中不同值的数量。当规划器只有单列统计数据时, 如果组合多个列(例如,对于GROUP BY a, b), 对不同值个数的估计常常是错误的,导致它选择不好的计划。

为了改进这种估计,ANALYZE可以为列组收集n个不同的统计数据。 与以前一样,对每个可能的列组都进行此操作是不切实际的, 因此仅针对那些出现在由ndistinct 选项定义的统计信息对象中的列组收集数据。 将针对列出的列中的两列或更多列的每个可能组合收集数据。

继续前面的例子,邮政编码表中的n个不同的计数可能如下所示:

CREATE STATISTICS stts2 (ndistinct) ON zip, state, city FROM zipcodes;

ANALYZE zipcodes;

SELECT stxkeys AS k, stxndistinct AS nd
  FROM pg_statistic_ext
  WHERE stxname = 'stts2';
-[ RECORD 1 ]--------------------------------------------------------
k  | 1 2 5
nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}
(1 row)

这表明有三种组合的列有33178个不同的值:ZIP code和state; ZIP code和city;ZIP code、city和state(事实上,他们都是平等的, 因为ZIP code本身在此表中是独一无二的)。另一方面, city与state的组合只有27435个不同的值。

建议仅在实际用于分组的列的组合上创建ndistinct统计信息对象, 并且对组数进行错误估计会导致计划错误。否则,ANALYZE周期只会被浪费。