关于Greenplum数据库中的数据库统计信息

Greenplum数据库中通过ANALYZE命令收集的统计信息的概述。

统计信息是描述数据库中数据的元数据。 查询优化器需要最新的统计信息以便为查询选择最好的执行计划。 例如，如果一个查询连接两个表并且其中的一个必须被广播到所有的Segment，优化器可以选择两个表中较小的那一个来最小化网络流量。

优化器使用的统计信息由ANALYZE命令计算并且保存在系统目录中。有三种方式开始一个分析操作：

  • 用户可以直接运行ANALYZE命令。
  • 用户可以在数据库外部从命令行运行analyzedb管理工具。
  • 当在没有统计信息的表上执行DML操作或者一个DML操作修改的行数超过指定阈值时，会触发一次自动的分析操作。

这些方法会在下面的小节中描述。VACUUM ANALYZE命令是另一种启动分析操作的方式，但是不鼓励使用它，因为清理和分析是两种不同目的的不同操作。

计算统计信息需要消耗时间和资源，因此Greenplum数据库通过在大型表的采样上计算统计信息来得到估计值。 在大部分情况下，默认的设置提供了足以为查询生成正确执行计划的信息。 如果产生的统计信息没有产生最优的查询执行计划，管理员可以调节配置参数通过提高样本尺寸或者系统目录中保存的统计信息粒度来产生更准确的统计信息。 产生更准确的统计信息需要CPU和存储代价并且不一定能产生更好的计划，因此重点是查看解释计划并且测试查询性能来确保额外的统计信息代价能导致更好的查询性能。

Parent topic: Greenplum数据库概念

系统统计信息

表尺寸

查询规划器想要最小化执行查询所需的磁盘I/O和网络流量，它会使用必须被处理的行数以及查询必须访问的磁盘页面数的估计值。 用于生成这些估计值的数据是pg_class系统表列reltuples和relpages，它们分别包含上一次VACUUM或ANALYZE命令运行时的行数和页面数。 随着行被增加或删除，这些数字变得越来越不准确。 不过，总是可以从操作系统拿到准确的磁盘页面计数，因此只要reltuples与relpages的比例不发生显著变化，优化器就能够产生对选择正确的查询执行计划足够准确的行数估计。

当reltuples列与SELECT COUNT(*)返回的行计数显著不同时，应该执行一次分析来更新统计信息。

当一个REINDEX命令完成了重建一个索引时，relpages和reltuples列被设置为零。应该在基表上运行ANALYZE命令以更新这些列。

pg_statistic系统表和pg_stats视图

pg_statistic系统表保持在每个数据库表上最后一次ANALYZE操作的结果。其中为每一个表的每一列都有一行。行有下面的列：

starelid

该列所属的表或索引的对象ID。

staattnum

所描述列的编号，从1开始。

stainherit

如果值是true，统计信息不仅包括指定的relation，还包括继承的子列。

stanullfrac

列中为空的项的分数值。

stawidth

非空项的平均存储宽度，单位是字节。

stadistinct

一个正数，它是列中可区分值的数量估计。这个数字预计并不会随着行数变化。 一个负值是可区分值数量除以行数，也就是该列中有可区分值的行的比例取负值。 当可区分值数量随行数增加时使用这种形式。例如，一个唯一列的n_distinct值为-1.0。 平均宽度超过1024的列被认为是唯一的。

stakindN

一个代码数字，它表示存储在pg_statistic行第N个槽中的统计信息类型。

staopN

用来得到第N个槽中统计信息的操作符。例如，一个直方图槽会显示 < 操作符，它定义数据的排序顺序。

stanumbersN

float4数组，包含第N个槽的合适类型的数字统计信息，如果槽类型不涉及数字值则为NULL。

stavaluesN

第N个槽的合适类型的列数据值，如果该槽类型不存储任何数据值则为NULL。 每一个数组的元素值实际是指定列的数据类型，因此没有办法比使用anyarray更具体地定义这些列的类型。

为一个列收集的统计信息随着不同数据类型变化，因此pg_statistic表中存储适合于四个槽中数据类型的统计信息，每个槽由四个列组成。 例如，第一个槽通常包含一列的最常见值，它由列stakind1、staop1、stanumbers1和stavalues1组成。

stakindN列每个都包含一个数字代码，用于描述存储在其插槽中的统计信息的类型。 从1到99的stakind代码编号保留给核心PostgreSQL数据类型。 Greenplum数据库使用代码编号 1，2，3，4，5，和99。0的意思是槽未使用。 下面的表格描述了为三种代码存储的统计信息类型。

Table 1. pg_statistic中”槽”的内容
stakind代码描述
1最常见值（MCV）槽
  • staop包含”=”操作符的对象ID，它被用来决定值是否相同。
  • stavalues包含一个数组，其中是该列中出现的K个最常见非空值。
  • stanumbers包含stavalues数组中值的频度（总行数的分数）。
值按照频度降序排序。因为数组是可变尺寸的，K可以由统计收集器选择。 要被加入到stavalues数组中，值必须出现超过一次。唯一列没有MCV槽。
2直方图槽 – 描述标量数据的分布。
  • staop是”<”操作符的对象ID，它描述排序顺序。
  • stavalues包含M（其中M>=2）个非空值，它们将非空的列数据值划分成M-1个群体数量大致相等的箱子。第一个stavalues项是最小值而最后最后一个是最大值。
  • stanumbers没有被使用且应该为NULL。

如果也提供了一个最常见值的槽，那么该直方图描述的是将MCV数组中列出的值移除后的数据分布（在技术上的说法是一个压缩直方图）。 这允许更精确地表示一个有一些非常常见值的列的分布。 在一个只有一些可区分值的列中，有可能MCV列表就描述了整个数据群体，在这种情况下直方图缩小为空并且应该被省略。

3相关关系槽 – 描述表元组物理顺序和列数据值顺序之间的相关关系。
  • staop是”<”操作符的对象ID。与直方图一样，理论上可能会出现多于一项。
  • stavalues未被使用并且应该为空。
  • stanumbers包含一个单项，它是数据值的序列和它们实际元组位置序列之间的相关系数。系数范围从+1到-1。
4最常见的元素插槽 - 类似于最常见值（MCV）插槽，除了它存储列值的最常见非空元素。 当列数据类型是数组或具有可识别元素的其他类型（例如，tsvector）时，这非常有用。
  • staop包含适合于元素类型的相等运算符。
  • stavalues包含最常见的元素值。
  • stanumbers包含共同的元素频率。

频率测量为元素值出现的非空行的分数，而不是所有行的频率。 此外，值将按元素类型的默认顺序排序（以支持特定值的二分查找）。 由于这会将最小和最大频率放在不稳定点上，因此有两个额外的stanumbers成员可以保存最小和最大频率的副本。 可选地，可以存在保持空元素的频率的第三额外成员（频率以相同的术语表示：包含至少一个空元素的非空行的分数）。 如果省略此成员，则假定该列不包含NULL元素。

Note: 注意：对于tsvector列，stavalues元素的类型为text，即使它们在tsvector中的表示不完全是文本。
5不同的元素计数直方图槽 - 描述了数组类型列的每一行中存在的不同元素值的数量的分布。 仅考虑非空行，并且仅考虑非空元素。
  • staop包含适合于元素类型的相等运算符。
  • stavalues未使用且应为NULL。
  • stanumbers包含有关不同元素的信息。 stanumbers的最后一个成员是所有非空行上的不同元素值的平均计数。 前面的M（其中M> = 2）成员形成直方图，该直方图将不同元素计数的群体划分为大致相等群体的M-1个区间。 第一个是观察到的最小计数，最后一个是最大值。
99超级日志槽 - 对于分区表的子叶分区，存储为采样数据创建的超级日志计数器。 hyperloglog_counter数据结构转换为bytea并存储在pg_statistic目录表的stavalues5插槽中。

pg_stats视图以一种友好的格式表示pg_statistic的内容。pg_stats视图有下列列：

schemaname

包含该表的方案名称。

tablename

该表的名称。

attname

这行所描述的列名。

inherited

如果为true，统计信息包含继承的子列。

null_frac

为空的列项所占的比例。

avg_width

该列中项的平均存储宽度（以字节为单位），计算方法是avg(pg_column_size(column_name))。

n_distinct

一个正数是该列中可区分值的数量估计。这个数字预计并不会随着行数变化。 一个负值是可区分值数量除以行数，也就是该列中有可区分值的行的比例取负值。 当可区分值数量随行数增加时使用这种形式。例如，一个唯一列的n_distinct值为-1.0。 平均宽度超过1024的列被认为是唯一的。

most_common_vals

包含该列中最常见值的数组，如果没有值看起来更常见则为空。 如果n_distinct列为-1，则most_common_vals为空。 这个数组的长度小于实际的可区分列值的数量或者default_statistics_target配置参数的值。 对一个列可以使用ALTER TABLE table SET COLUMN column SET STATISTICS N覆盖值的数量。

most_common_freqs

包含most_common_vals数组中值的频率。它是一个值的出现次数除以总行数。 这个数组和most_common_vals数组的长度相等。如果most_common_vals为空，则它也为空。

histogram_bounds

一个值数组，它把列值划分成大约相同尺寸的分组。只有对该列有一个max()聚集函数时才能定义直方图。 直方图中分组的数量等于most_common_vals数组的尺寸。

correlation

Greenplum数据库不计算相关关系统计信息。

most_common_elems

包含最常见元素值的数组。

most_common_elem_freqs

包含公共元素频率的数组。

elem_count_histogram

一个数组，描述数组类型列的每一行中存在的不同元素值的数量的分布。

新创建的表和索引没有统计信息。可以用gp_stats_missing视图检查缺少统计信息的表，该视图位于gp_toolkit方案中：

  1. SELECT * from gp_toolkit.gp_stats_missing;

采样

在为大型表计算统计信息时，Greenplum数据库通过采样基表来创建一个较小的表。如果表被分过区，会从所有的分区取得抽样。

更新统计信息

运行不带参数的ANALYZE会为数据库中的所有表更新统计信息。 这可能会耗费非常长的时间，因此更好的方式是在数据被改变后有选择地分析表。 用户还可以分析一个表中的列子集，例如连接中、WHERE子句、SORT子句、GROUP BY子句或者HAVING子句中用到的列。

如果采样中包含空页面，分析一个严重膨胀的表可能生成不好的统计信息，因此最好的做法是在分析一个膨胀了的表之前先清理它。

运行ANALYZE命令的详情请见Greenplum数据库参考指南中的SQL命令参考。

运行analyzedb命令的细节请参考Greenplum数据库管理工具参考。

分析分区表

当ANALYZE命令被运行在一个分区表上时，它会逐个分析每一个叶子层子分区。 用户可以只在新的或者更改过的分区表上运行ANALYZE以避免分析没有变化过的分区。

analyzedb命令行工具会自动跳过未更改的分区。它还会运行并发会话，这样它可以并发地分析几个分区。 默认它会运行五个会话，但会话的数量可以用-p命令行选项设置为1至10。 analyzedb每次运行时，它会在Master数据目录中的db_analyze目录下为追加优化表和分区保存状态信息。 下一次它运行时，analyzedb会把每个表的当前状态与其保存状态相比较，并且跳过没有更改的表或分区。堆表总是会被分析。

如果GPORCA被启用（默认），用户还需要运行ANALYZE ROOTPARTITION来刷新根分区的统计信息。 GPORCA要求在分区表根层的统计信息。传统优化器不使用这些统计信息。

分析分区表的时间类似于分析具有相同数据的非分区表的时间，因为ANALYZE ROOTPARTITION不收集叶分区上的统计信息（仅对数据进行采样）。 analyzedb工具默认会更新根分区统计信息

Greenplum数据库服务器配置参数optimizer_analyze_root_partition会影响何时在分区表的根分区上收集统计信息。 如果参数为on（缺省值），则在运行ANALYZE时，不需要ROOTPARTITION关键字来收集根分区的统计信息。 在根分区上运行ANALYZE时，或者在分区表的子叶子分区上运行ANALYZE并且其他子叶子分区具有统计信息时，将收集根分区统计信息。 如果参数关闭，则必须运行ANALYZE ROOTPARTITION以收集根分区统计信息。

如果您不打算使用GPORCA对分区表执行查询（将服务器配置参数optimizer设置为关闭）， 则还可以将服务器配置参数optimizer_analyze_root_partition设置为off，以限制ANALYZE更新根分区统计信息的时间。

配置统计信息

有几个选项可以配置Greenplum数据库的统计信息收集。

统计信息目标

统计信息目标是一个列的most_common_vals、most_common_freqs以及histogram_bounds数组的尺寸。 默认情况下，目标是25。默认目标可以通过设置一个服务器配置参数来更改，对任意列可以使用ALTER TABLE命令设置目标。 较大的值会增加执行ANALYZE所需的时间，但是可能会增加传统查询优化器（规划器）估计值的质量。

可以通过设置default_statistics_target服务器配置参数将系统的默认统计信息目标设置为一个不同的值。 默认值通常是足够的，用户只应该在测试证明新的目标能够改进查询计划时才调整目标。 例如，要将默认的统计信息目标从100增加到150，用户可以使用gpconfig工具：

  1. gpconfig -c default_statistics_target -v 150

可以使用ALTER TABLE命令设置各列的统计信息目标。 例如，某些查询可以通过增加某些列的目标来改进，尤其是具有不规则分布的列。 对于从不参与查询优化的列，您可以将目标设置为零。 当目标是0，ANALYZE会忽略该列。 例如，以下ALTER TABLE命令将emp表中notes列的统计信息目标设置为零：

  1. ALTER TABLE emp ALTER COLUMN notes SET STATISTICS 0;

统计信息目标可以被设置为0到1000，或者把它设置为-1以回到使用系统的默认统计信息目标。

在父分区表上设置统计信息目标会影响子分区。 如果在父表上把某些列的统计信息目标设置为0，对所有子分区也会把相同列的统计信息目标设置为0。 不过，如果用户后来增加或者交换了另一个子分区，新的子分区将会使用默认统计信息目标或者之前的统计信息目标（在交换的情况下）。 因此，如果用户增加或者交换子分区，应该在新的子表上设置统计信息目标。

自动统计收集

Greenplum数据库可以被设置为在没有统计信息或者在执行特定操作后发生显著改变的表上自动地运行ANALYZE。 对于分区表，自动统计收集只在操作直接运行在叶子表上时才被触发，然后也只会分析这个叶子表。

自动统计收集有三种模式：

  • none 禁用自动统计收集。
  • 当任意一个CREATE TABLE AS SELECT、INSERT或者COPY命令在没有现存统计信息的表上执行时，on_no_stats模式会为该表触发一次分析操作。
  • 当任意一个CREATE TABLE AS SELECT、UPDATE、DELETE、INSERT或者COPY命令在表上执行并且被影响的行数超过gp_autostats_on_change_threshold配置参数所定义的阈值时， on_change模式会为该表触发一次分析操作

对于发生在一个过程语言函数内的命令以及在一个函数之外执行的命令，自动统计收集模式是单独设置的：

  • gp_autostats_mode配置参数控制函数外自动统计收集的行为并且默认被设置为on_no_stats。
  • gp_autostats_mode_in_functions参数控制表操作在一个过程语言函数内执行时的行为并且默认被设置为none。

通过on_change模式，只有受影响的行数超过gp_autostats_on_change_threshold配置参数所定义的阈值时才会触发ANALYZE。 这个参数的默认值是一个非常高的值2147483647，它实际上禁用了自动统计收集。 用户必须将该阈值设置得较低来启用它。on_change模式可能会触发大型的、预期之外的分析操作，它们可能会中断系统，因此不推荐在全局范围内设置它。 在一个会话中它可能会有用，例如在一次装载后自动分析一个表。

要在函数之外禁用自动统计收集，将gp_autostats_mode参数设置为none：

  1. gpconfigure -c gp_autostats_mode -v none

要在函数中为没有统计信息的表启用自动统计收集，将gp_autostats_mode_in_functions改成on_no_stats：

  1. gpconfigure -c gp_autostats_mode_in_functions -v on_no_stats

如果想要记录自动统计收集操作，可将log_autostats系统配置参数设置为on。