Postgres10高性能开发（3）统计

た入场券 2023-07-10 12:46 96阅读 0赞

# 统计 #

解释计划通过统计分析各种方式的成本来选择"最优"的执行路径

VACUUM, ANALYZE或一些DDL操作（如CREATE INDEX）可以重新生成统计

## 单列统计 ##

#### pg\_class ####

记录表、索引中的行数

VACUUM, ANALYZE或一些DDL操作（如CREATE INDEX）生成统计

SELECT relname,relkind,reltuples,relpages from pg_class where relname like 'bms_t_group_hotel%'
    
    #Result
    relname                 relkind  reltuples   relpages
    bms_t_group_hotel	    r	     28	         2
    bms_t_group_hotel_pkey	i	     28	         2

*  relname：统计对象，包括表、视图、索引等
 *  relkind：
    
     *  i表示索引
     *  r表示表
     *  S表示序列
     *  t标识TOAST表
     *  v标识视图
     *  m标识物化视图
     *  c标识组合类型
     *  f表示外部表
     *  p表示分区表
 *  reltuples：表中行数
 *  relpages：磁盘中占用的页数

#### pg\_statistic ####

记录表内容的统计信息

ANALYZE生成统计

pg\_statistic的数据可通过pg\_stats视图查看

> https://www.postgresql.org/docs/10/view-pg-stats.html

*  tablename:表名
 *  attname： 列名
 *  null\_frac： 列中null值比例
 *  avg\_width：列平均宽度（单位byte）
 *  n\_distinct：
    
     *  如果值大于0，则为列中distinct值的数量；
     *  如果值小于0，则其绝对值为distinct值数量/总行数
 *  most\_common\_vals：列中最常见的值列表。（如果没有值比任何其他值更常见，则为空。）
 *  most\_common\_freqs：最常见值的频率列表，即每个值的出现次数除以总行数
 *  histogram\_bounds：一个值列表，用于将列的值划分为大致相等的总体组
 *  correlation： 物理行排序与列值的逻辑排序之间的统计相关性，范围从-1到+1，值越接近-1或+1则相关性越低
 *  most\_common\_elems：为数组类型列时， 最常出现在列值内的非null元素值列表
 *  most\_common\_elems\_freqs：为数组类型列时， 最常出现在列值内的非null元素值的频率
 *  elem\_count\_histogram：为数组类型列时， 列值中不同非空元素值计数的直方图，后跟不同非空元素的平均数

## 扩展统计 ##

如果查询针对多个字段，单列统计只针对了单个列，无法评估多列值组合下的数据区分度。扩展统计可以解决此问题。

由于列的组合数量非常大，扩展统计需要手动创建

#### 函数依赖 ####

当字段A和字段B存在強依赖关系，例如邮编和城市之间有依赖关系，可以使用功能依赖

CREATE STATISTICS stts(dependencies) ON zip, city FROM zipcodes;

> zip能完全确定city，city对应多个zip，因此zip要放在前面

函数依赖当前仅在将列与常量值进行比较的简单相等条件时应用

SELECT * FROM zipcodes WHERE city ='合肥'AND zip = '230001';

此时解释计划评估时将只使用zip评估区分度，忽略city

#### 多变量N-Distinct计数 ####

为指定的多个列收集统计信息

CREATE STATISTICS stts2 (ndistinct) ON zip, state, city FROM zipcodes;
    
    ANALYZE zipcodes;
    
    SELECT stxkeys AS k, stxndistinct AS nd
      FROM pg_statistic_ext
      WHERE stxname = 'stts2';
    -[ RECORD 1 ]--------------------------------------------------------
    k  | 1 2 5
    nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}
    (1 row)

统计结果预计

*  zip和state有33178组DISTINCT值
 *  zip和city有33178组DISTINCT值
 *  state和city有27435组DISTINCT值
 *  zip、state和state有33178组DISTINCT值

建议只在分组列组合上创建统计对象