更新统计信息

背景信息

ANALYZE语句可收集与数据库中表内容相关的统计信息，统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，以生成最有效的执行计划。

建议在执行了大批量插入/删除操作后，例行对表或全库执行ANALYZE语句更新统计信息。目前默认收集统计信息的采样比例是30000行（即：guc参数default_statistics_target默认设置为100），如果表的总行数超过一定行数（大于1600000），建议设置guc参数default_statistics_target为-2，即按2%收集样本估算统计信息。

对于在批处理脚本或者存储过程中生成的中间表，也需要在完成数据生成之后显式的调用ANALYZE。

对于表中多个列有相关性且查询中有同时基于这些列的条件或分组操作的情况，可尝试收集多列统计信息，以便查询优化器可以更准确地估算行数，并生成更有效的执行计划。

生成统计信息

• 更新单个表的统计信息。

  1	ANALYZE tablename;

• 更新全库的统计信息。

  1

  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，直方图histgram等。

收集条件：当数据量发生较大变化，默认是变化10%，认为数据特征已经有了变化，需要重新收集统计信息。

总体策略：开启动态采样保证统计信息及时性，开启轮询采样保证统计信息持久化，查询性能敏感（秒级响应）靠手动采样。

基本规则

场景和策略

列举了常见的数据加工场景和对应的统计信息收集策略。

begin;
truncate table or partition;
copy/merge/insert overwrite
ANALYZE (light) tablename;
end;

资源消耗

准确性及可靠性

运维监控

GaussDB(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;