Postgres10高性能开发（3）统计

统计

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

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

SELECT attname, inherited, n_distinct,
       array_to_string(most_common_vals, E'\n') as most_common_vals
FROM pg_stats
WHERE tablename = 'road';

#RESULT
 attname | inherited | n_distinct |          most_common_vals
---------+-----------+------------+------------------------------------
 name    | f         |  -0.363388 | I- 580                        Ramp+
         |           |            | I- 880                        Ramp+
         |           |            | Sp Railroad                       +
         |           |            | I- 580                            +
         |           |            | I- 680                        Ramp
 name    | t         |  -0.284859 | I- 880                        Ramp+
         |           |            | I- 580                        Ramp+
         |           |            | I- 680                        Ramp+
         |           |            | I- 580                            +
         |           |            | State Hwy 13                  Ramp

• 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值

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