合 Greenplum中的统计信息

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

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

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

优化器使用的统计信息由ANALYZE命令计算并且保存在系统目录中。

GP统计信息分析有三种方式：

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

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

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

下面列出分析统计信息相关的命令：

系统统计信息

表尺寸

查询规划器想要最小化执行查询所需的磁盘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的意思是槽未使用。 下面的表格描述了为三种代码存储的统计信息类型。

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方案中：

采样

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

更新统计信息

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

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

分析分区表

当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数组的尺寸。 默认情况下，目标是100。默认目标可以通过设置一个服务器配置参数来更改，对任意列可以使用ALTER TABLE命令设置目标。 较大的值会增加执行ANALYZE所需的时间，但是可能会增加传统查询优化器（规划器）估计值的质量。

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

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

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

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

default_statistics_target参数

在Greenplum数据库中，default_statistics_target是一个参数，该参数用于控制数据库中每个列的默认统计目标值。统计目标值是指在执行ANALYZE命令时，数据库为每个列收集的统计信息的样本大小。这些统计信息用于查询优化器生成查询计划。

默认情况下，default_statistics_target参数的值为100，表示数据库将在执行ANALYZE命令时收集每个列的100个样本。您可以根据需要调整此参数的值。较高的值会导致更准确的统计信息，但也会增加ANALYZE命令的执行时间。较低的值可能会导致统计信息的不准确，从而影响查询优化器生成的查询计划。

要修改default_statistics_target参数的值，可以使用ALTER DATABASE命令或在特定数据库会话中使用SET命令进行修改。以下是示例：

使用ALTER DATABASE命令：

使用SET命令（在数据库会话中）：

请注意，修改此参数的值可能会影响数据库性能和查询优化，请谨慎进行更改，并在生产环境中进行测试和评估。

自动统计收集

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

当自动统计信息收集被启用时，在某个阈值的行被影响（on_change）时或者一个新产生的表没有统计信息（on_no_stats）时，用户可以自动地在INSERT、UPDATE、 DELETE、COPY或者CREATE TABLE...AS SELECT语句的同一个事务中自动运行ANALYZE。要启用这一特性，在用户的Greenplum Master的postgresql.conf文件中设置下列服务器配置参数并且重启数据库：

• gp_autostats_mode
• gp_autostats_mode_in_functions
• log_autostats
• gp_autostats_on_change_threshold

警告：取决于用户的数据库操作的特质，自动统计信息收集可能会带来负面的性能影响。请仔细评估on_no_stats的默认设置是否适合用户的系统。

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

• gp_autostats_mode配置参数控制函数外自动统计收集的行为，默认被设置为on_no_stats。
• gp_autostats_mode_in_functions参数控制表操作在一个过程语言函数内执行时的行为，默认被设置为none。函数可能是用户定义的函数（UDF）或其他内置函数。

参数gp_autostats_mode和gp_autostats_mode_in_functions的可选值为：

• none： 禁用自动统计收集。
• on_no_stats：当任意一个CREATE TABLE AS SELECT、INSERT或者COPY命令在没有现存统计信息的表上执行时，on_no_stats模式会为该表触发一次分析操作。实际上默认情况下，我们对空表写入数据后， Greenplum 也会自动帮我们收集统计信息，不过之后再写入数据，就需要手动收集统计信息了。
• on_change：当任意一个CREATE TABLE AS SELECT、UPDATE、DELETE、INSERT或者COPY命令在表上执行并且被影响的行数超过gp_autostats_on_change_threshold配置参数所定义的阈值时， on_change模式会为该表触发一次分析操作。该动作是立即执行的。

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

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

自动收集统计信息示例：

用ANALYZE更新统计信息

良好查询性能的最重要的先决条件是从表的正确统计信息开始。用ANALYZE语句更新统计信息 让查询规划器能生成最优的查询计划。当表被分析时，有关数据的信息被存储在系统目录表中。如果存储的信息过时， 规划器可能会生成低效的执行计划。

说明

ANALYZE是Greenplum提供的收集统计信息的命令。

ANALYZE支持三种粒度，列，表，库，如下：

限制

ANALYZE会给目标表加SHARE UPDATE EXCLUSIVE锁，也就是与UPDATE，DELETE，还有DDL语句冲突。

速度

ANALYZE是一种采样统计算法，通常不会扫描表中所有的数据，但是对于大表，也仍会消耗一定的时间和计算资源。

采样统计会有精度的问题，因此Greenplum也提供了一个参数default_statistics_target，调整采样的比例。简单说来，这个值设置得越大，采样的数量就越多，准确性就越高，但是消耗的时间和资源也越多。

直接修改服务器的参数会影响整个集群，通常不建议这样操作。如果确实有需要，可以尝试只修改某列的对应参数，如下：

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。