良好查询性能最重要的先决条件是从表的正确统计信息开始。用 ANALYZE 语句更新统计信息让查询规划器能生成最优的查询计划。当表被分析时,有关数据的信息被存储在系统目录表中。如果存储的信息过时,规划器可能会生成低效的执行计划。
有选择地生成统计信息
不带参数运行 ANALYZE 会为数据库中所有的表更新统计信息。这样操作运行时间可能会非常长,因此不推荐这样做。当数据被改变时,使用者应该有选择地 ANALYZE 表或者使用 analyzedb 工具。
在大型表上运行 ANALYZE 可能需要很长时间。如果在非常大的表的所有列上运行 ANALYZE 行不通,使用者可以只使用 ANALYZE table(column,...)为选择的列生成统计信息。确保包括用在连接、WHERE 子句、SORT 子句、GROUP BY 子句或者 HAVING 子句中的列都被收集了统计信息。
对于一个分区表,使用者可以只在更改过的分区(例如,使用者增加一个分区)上运行 ANALYZE。 注意对于分区表,使用者可以在父(主)表上或者叶子节点(实际存储数据和统计信息的分区文件)上运行 ANALYZE。 子分区表的中间文件没有存储数据或统计信息,因此在其上运行 ANALYZE 没有效果。使用者可以在 pg_partitions 系统目录中寻找分区表的名字:
SELECT partitiontablename from pg_partitions WHERE tablename='parent_table';
提升统计信息质量
在生成统计信息所花的时间和统计信息的质量或者准确性之间存在着权衡。
为了允许大型表能在合理的时间内被分析完,ANALYZE 会对表内容做随机采样而不是检查每一行。 要对所有表列增加采样,可调整 default_statistics_target 配置参数。其目标值取值范围从 1 到 1000,默认的目标值是 100。default_statistics_target 变量默认会被应用到所有的列。 更大的目标值会增加执行 ANALYZE 所需的时间,但是可以提升查询规划器的评估质量,对于带有不规则数据模式的列尤其如此。default_statistics_target 可以在 master 或者会话级别设置,并且要求重新载入配置。
何时运行 ANALYZE
在下列时机运行 ANALYZE:
- 导入数据后。
- CREATE INDEX 操作后。
- 在显著更改底层数据的 INSERT、UPDATE 以及 DELETE 操作之后。
ANALYZE 仅在表上要求一个读锁,因此它可以与其他数据库活动并行运行。但不要在执行导入、INSERT、UPDATE、DELETE 以及 CREATE INDEX 操作期间运行 ANALYZE。
配置统计信息自动收集
gp_autostats_mode 配置参数与 gp_autostats_on_change_threshold 参数一起决定何时触发自动分析操作。当自动统计信息收集被触发时,规划器会为查询增加一个 ANALYZE 步骤。
gp_autostats_mode 默认为 on_no_stats,这会为任何没有统计信息的表上的 CREATE TABLE AS SELECT、INSERT 或者 COPY 操作触发统计信息收集。
把 gp_autostats_mode 设置为 on_change 时,只有当受影响的行数超过由gp_autostats_on_change_threshold 定义的阈值时才会触发统计信息收集,该阈值参数的默认值为 2147483647。on_change 设置下能触发自动统计信息收集的操作有: CREATE TABLE AS SELECT、UPDATE、DELETE、INSERT 以及 COPY。 CREATE TABLE AS SELECT、UPDATE、DELETE、 INSERT 以及 COPY。
将 gp_autostats_mode 设置为 none 会禁用自动统计信息收集。
对于分区表,如果数据从分区表的顶层父表插入,则自动统计信息收集不会被触发。但是如果数据直接被插入到分区表的叶子表(存储数据的地方)中,则自动统计信息收集会被触发。