# 更新统计信息 在数据库中，统计信息是规划器生成计划的源数据。没有收集统计信息或者统计信息陈旧会造成执行计划严重劣化，从而导致性能问题。 #### 背景信息 ANALYZE语句可收集与数据库中表内容相关的统计信息，统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，以生成最有效的执行计划。 建议在执行了大批量插入/删除操作后，例行对表或全库执行ANALYZE语句更新统计信息。目前默认收集统计信息的采样比例是30000行（即：guc参数default_statistics_target默认设置为100），如果表的总行数超过一定行数（大于1600000），建议设置guc参数default_statistics_target为-2，即按2%收集样本估算统计信息。 对于在批处理脚本或者存储过程中生成的中间表，也需要在完成数据生成之后显式的调用ANALYZE。 对于表中多个列有相关性且查询中有同时基于这些列的条件或分组操作的情况，可尝试收集多列统计信息，以便查询优化器可以更准确地估算行数，并生成更有效的执行计划。 #### 生成统计信息 - 更新单个表的统计信息。 ``` ANALYZE tablename; ``` - 更新全库的统计信息。 ``` ANALYZE; ``` - 多列统计信息相关操作。 - 收集tablename表的column_1、column_2列的多列统计信息 ``` ANALYZE tablename ((column_1, column_2)); ``` - 添加tablename表的column_1、column_2列的多列统计信息声明 ``` ALTER TABLE tablename ADD STATISTICS ((column_1, column_2)); ``` - 收集单列统计信息，并收集已声明的多列统计信息 ``` ANALYZE tablename; ``` - 删除tablename表的column_1、column_2列的多列统计信息或其声明 ``` ALTER TABLE tablename DELETE STATISTICS ((column_1, column_2)); ``` ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/notice_3.0-zh-cn.png) - 在使用ALTER TABLE tablename ADD STATISTICS语句添加了多列统计信息声明后，系统并不会立刻收集多列统计信息，而是在下次对该表或全库进行ANALYZE时，进行多列统计信息的收集。如果想直接收集多列统计信息，请使用ANALYZE命令进行收集。 - 使用EXPLAIN查看各SQL的执行计划时，如果发现某个表SEQ SCAN的输出中rows=10，rows=10是系统给的默认值，有可能该表没有进行ANALYZE，需要对该表执行ANALYZE。 #### 提升统计信息质量 ANALYZE是按照随机采样算法从表上采样，根据样本计算表数据特征。采样数可以通过配置参数default_statistics_target进行控制，default_statistics_target取值范围为-100\~10000，默认值为100。 - 当default_statistics_target \> 0时；采样的样本数为300\*default_statistics_target，default_statistics_target取值越大，采样的样本也越大，样本占用的内存空间也越大，统计信息计算耗时也越长。 - 当default_statistics_target \< 0时，采样的样本数为 (default_statistics_target)/100\*表的总行数，default_statistics_target取值越小，采样的样本也越大。当default_statistics_target \< 0时会把采样数据下盘，不存在样本占用的内存空间的问题，但是因为样本过大，计算耗时长的问题同样存在。 default_statistics_target \< 0时，实际采样数是 (default_statistics_target)/100\*表的总行，所以又称之为百分比采样。 #### 自动收集统计信息 当配置参数autoanalyze打开时，查询语句走到优化器发现表不存在统计信息或数据变化超过阈值时，会自动触发统计信息收集，以满足优化器的需求。 基于代价的优化器模型（CBO, cost base optimizer）中，统计信息决定了查询计划生成的好坏。因此，统计信息的及时有效很重要。 - 表级统计信息，存储在pg_class的relpages, reltuples中。 - 列级统计信息，存储在pg_statistics中，可以通过pg_stats视图查看。包括：NULL值比例，distinct值占比，高频值MCV，直方图histogram等。 **收集条件：**当数据量发生较大变化，默认是变化10%，认为数据特征已经有了变化，需要重新收集统计信息。 **总体策略：**开启动态采样保证统计信息及时性，开启轮询采样保证统计信息持久化，查询性能敏感（秒级响应）靠手动采样。 #### 基本规则 表1统计信息收集相关基础功能 | 功能 | 介绍 | 特点 | 约束 | |:---|:---|:---|:---| | 手动采样 | 作业中修改大量数据后，手动执行ANALYZE。 | - normal模式，统计信息存系统表，全局共享。四级锁，同一张表不能并发。 - light模式，统计信息存内存，全局共享。一级锁，同一张表可以并发。 - force模式，在锁定统计信息情况下，也可以强制采样，行为与normal一致。 语法：ANALYZE tablename; ANALYZE (light\|force) tablename; | - | | 轮询采样 | 后台线程，根据阈值轮询维护统计信息。 | 仅支持normal模式，统计信息存系统表，共享。四级锁，同一张表不能并发。 相关GUC参数： - autovacuum - autovacuum_mode - autovacuum_analyze_threshold - autovacuum_analyze_scale_factor | 异步轮询触发。 | | 动态采样 | 查询解析时，根据阈值，用几十秒的代价实时维护统计信息。 | - normal模式，统计信息存系统表，全局共享。四级锁，同一张表不能并发。 - light模式，统计信息存内存，全局共享。一级锁，同一张表可以并发。 相关GUC参数： - autoanalyze - autoanalyze_mode | 随查询实时触发。轻量化时依赖轮询采样进行持久化。 | | 强制采样 | SQL中通过hint，强制每次查询都收集统计信息。 | 用于数据特征敏感场景，确保查询具有最新的统计信息。 用法：select /\*+ lightanalyze (t1 1) \*/ from t1; 1：强制采样，0：禁止采样 | 需要修改SQL。 | | 分区统计信息 | 按分区收集，仅收增量，自动合并全局信息。 | 用于超大分区表的场景，确保分区剪枝后的查询代价估算准确。 | 多占存储，信息更准。 | | 多列统计信息 | 根据多个列组合收集统计信息。 | 用于多列同时过滤场景，确保对多列组合的查询代价估算准确。 | 手动识别，临时表方式。 | | 表达式统计信息 | 根据表达式函数对某列收集统计信息。 | 用于批量的表达式过滤的场景，确保查询表达式的查询代价估算准确。 | 手动识别。 | | 表达式索引信息 | 创建的表达式索引会自动收集统计信息。 | 用于点查的表达式过滤的场景，确保查询表达式的查询代价估算准确。 | 手动识别。 | | 冻结统计信息 | 将表级的统计信息冻结，防止发生变化。 | 用于数据特征极稳定场景，禁止采样，防止查询计划跳变。 用于数据特征极易变场景，强制采样，确保每次查询都采样。 参数：表级属性analyze_mode | - | | 修改统计信息 | 手动计算后，直接修改统计信息。 | 继续低采样率，但可以手动计算后进行校准。用法： select approx_count_distinct(col_name) from table_name; alter table set (n_distinct=xxx); | - | | 分区信息拷贝 | 可将旧分区的统计信息拷贝到新分区。 | 用于数据特征变化不大的分区表，减少统计信息收集的开销。 | - | | 统计信息推理 | 基于旧的统计信息自动推算更准确的统计信息。 | 通过GUC参数enable_extrapolation_stats设置。 | - | | 统计信息备份恢复 | 通过explain (stat on) 将统计信息备份成SQL。 | 用于场景复现或统计信息还原。 | 导出SQL的形式。 | #### 场景和策略 列举了常见的数据加工场景和对应的统计信息收集策略。 表2统计信息收集策略 | 场景 | 特点 | 策略 | |:---|:---|:---| | 流式增量加工 | 数据流式增量变化，无合理ANALYZE时机。 | 开启动态采样，查询按需自动收集统计信息，且全局共享。 | | 在线批量加工 （数据湖） | 数据加工与查询会并发，要求查询稳定。 | 开启动态采样，或一个事务中完成数据加工和ANALYZE。 ``` begin; truncate table or partition; copy/merge/insert overwrite ANALYZE (light) tablename; end; ``` | | 分区并行加工 | 不同分区并发加工数据。 | 开启"动态采样"或"手动light采样"，同表可并发收集 | | 宽表场景 | 百列以上的宽表。 | 1. 启动动态采样的自动谓词管理。 2. 仅收集前N列的统计信息。 3. 根据查询中常用谓词，可列级设置是否参与采样。 | | 大表场景 | - 表的数据量大，变化难以达到阈值。 - 统计信息易变。 | 调低动态采样触发阈值。 | | 特征敏感场景 | - 数据特征易变，查询计划不稳定。 - 需要强制收集。 | 1. 调低动态采样触发阈值。 2. 在SQL中通过HINT方式强制light动态采样。 3. 清空并冻结统计信息，查询每次都会重新收集且不共享。 | | 高并发场景 | 同一张表会高并发查询（10并发以上）。同时触发动态采样占用资源多。 | 1. 关闭并发，其它查询不使用最新统计信息。 2. 其它查询等最新统计信息生成，再执行查询（开发中）。 | | 流式性能敏感 | 流式加工，秒级查询或整体资源高水位。 | 表级或SQL级禁止动态采样，依靠后台轮询采样。 | | 批量性能敏感 | 批量加工，秒级查询或整体资源高水位。 | 加工时手动收集统计信息。 | #### 资源消耗 表3资源消耗 | 类别 | 详细分类 | 说明 | |:---|:---|:---| | CPU | 谓词列管理 | 自动管理谓词，仅对查询中使用的列收集统计信息。 手动屏蔽非谓词列，可手动设置哪些列不收集统计信息。 | | CPU | 超长列统计 | 可截断的数据类型，仅收取前1024字符计算统计信息。 | | IO | 默认采集3万条样本 | 与表大小无关，与列数，分区数，小CU数有关。 | | 内存 | Buffer占用 | 最多只占用cstore buffer中的一个槽位。 | | 内存 | 内存零拷贝 | 从buffer取出样本后，直接进行统计信息计算，无需组织成tuple再传递。 | | 内存 | 内存自适应 | 可配置为内存不足时自动转为使用临时表采样。动态采样为防止查询触发建临时表，不会自适应。 通过GUC参数analyze_stats_mode设置。 | | 内存 | 内存的大小 | 控制ANALYZE时最大使用内存，超过后或下盘或自动减少样本。 通过GUC参数maintenance_work_mem设置。 | | 锁 | 四级锁 | normal模式，分布式申请四级锁。与增删改不冲突，与DDL，VACUUM，ANALYZE，REINDEX冲突。 | | 锁 | 一级锁 | light模式，仅本地一级锁。仅与DDL冲突。 | #### 准确性及可靠性 表4准确性及可靠性 | 准确性及可靠性 | 分类 | 说明 | |:---|:---|:---| | 准确性 | 采样大小 | 可配置为按表大小自适应。由参数default_statistics_target控制。 | | 准确性 | 采样随机性 | - analyze_sample_mode参数设置新支持优化蓄水池和range采样随机性更优。 - random_function_version参数增强了随机数计算函数的随机性。 | | 准确性 | 全局共享 | 统计信息可以跨会话和跨节点进行共享。 | | 准确性 | 修改计数广播 | 后台线程会轮询全局检查修改计数并进行广播。 作业线程也可直接广播修改计数。通过参数tuple_change_sync_threshold设置。 跨CN修改和查询也影响不大，修改计数会异步方式广播同步。 | | 准确性 | 调整CU采样比 | CU填充率不高的场景，可调大CU采样比。通过列级参数cstore_cu_sample_ratio设置。 | | 准确性 | distinct固定 | 随机采样后distinct值不稳定场景，不调高采样率情况下，可进行distinct值固定。通过列级参数n_distinct设置。 | | 准确性 | 统计信息推算 | enable_extrapolation_stats参数可以控制估算失真时，基于旧的统计信息自动推算更准确的统计信息。 | | 可靠性 | CN故障 | 其它CN故障时，不影响动态采样，统计信息不同步，不影响当前CN查询的统计信息质量。 | | 可靠性 | CN恢复 | 其它CN恢复后，再次查询时会强制动态采样，并全局同步。 | | 可靠性 | DN故障 | 非当前逻辑集群的DN故障，不影响本逻辑集群的动态采样。 | #### 运维监控 DWS通过在ANALYZE命令后面追加注释的方式，详细展示了ANALYZE运行模式和各种执行阶段，主要通过以下视图体现： - 当前活跃会话视图pgxc_stat_activity的query字段。 - 当前线程等待视图pgxc_thread_wait_status的wait_status字段。 ANALYZE命令追加注释的内容格式为：--Action-RunMode-StatsMode-SyncMode - Action取值及含义： ``` {"begin", "finished", "lock FirstCN", "estimate rows", "statistics", "sample rows", "calc stats"}; ``` 分别表示：开始，结束，在向FirstCN申请锁，在一阶段估算行数信息，在二阶段真正执行ANALYZE，在采集样本，在计算统计信息。 - RunMode取值及含义： ``` {"manual", "backend", "normal runtime", "light runtime", "light runtime inxact", "light estimate rows", "light manual"}; ``` 分别表示：手动模式，后台轮询模式，normal动态采样，light动态采样，事务内的light动态采样，light仅估算函数，手动light模式。 - StatsMode取值及含义： ``` {"dynamic", "memory", "smptbl"}; ``` 分别表示：自适应选择内存或临时表放样本，仅使用内存放样本，仅使用临时表放样本。 - SyncMode取值及含义： ``` {"sync", "nosync"}; ``` 分别表示：向所有CN同步统计信息，不同步统计信息。 示例： ``` SELECT coorname,datid,datname,pid,usename,application_name,query_id,query FROM pgxc_stat_activity WHERE query like '%analyze%' and query not like '%application_name%'; coorname | datid | datname | pid | usename | application_name | query_id | query --------------+-------+----------+-----------------+-----------+------------------+-------------------+----------------------------------------- coordinator1 | 15676 | postgres | 139919333779200 | test | gsql | 73183493944770822 | analyze t_1; coordinator2 | 15676 | postgres | 140217336461056 | test | coordinator1 | 73183493944770822 | analyze public.t_1;--push stats-manual-memory-sync coordinator3 | 15676 | postgres | 139944245847808 | test | coordinator1 | 73183493944770822 | analyze public.t_1;--push stats-manual-memory-sync (3 rows) ``` #### 统计信息查看 - 查看动态采样的内存统计信息。 - 查内存中表级统计信息： ``` SELECT * FROM pv_runtime_relstats; ``` - 查内存中列级统计信息： ``` SELECT * FROM pv_runtime_attstats; ``` - 查看系统表中统计信息。 - 查系统表中表级统计信息： ``` select relname, relpages, reltuples from pg_class; ``` - 查系统表中列级统计信息： ``` SELECT * FROM pg_stats; ``` - 查看统计信息最近收集时间。 动态采样把统计信息放内存中，不更新该系统表的时间。 ``` SELECT * FROM pg_object; ```