postgresql 列默认值 与另一个值相同_浅谈PostgreSql数据统计的作用

作者： 高雪玉，中国PG分会志愿者，瀚高软件工程师

03

目录

1概述 2单列统计 3扩展统计 3.1函数依赖统计 3.2N-Distinct统计 3.3MCV Lists统计 3.3.1缺点 3.3.2优点

PART

1

概述

EXPLAIN展示了PostgreSQLPlanner生成的执行计划。Planner需要估计一个查询要检索的行数，这样才能对查询计划做出更好的选择。Planner收集到的数据统计是决定使用哪种规划的重要因素。   统计包含两大类：单列统计和扩展统计。 扩展统计包含以下3类，其中，MCV统计是PG12新增功能。 (1) 函数依赖统计 (2) N-Distinct统计 (3) MCV Lists统计(PG12新增)

                           

创建如下测试表格并插入数据，以便更直观的了解统计功能。

CREATE TABLE test (x int, y int);INSERT INTO test SELECT mod(i,3),mod(i,30) FROM generate_series (1,10000) s(i);ANALYZE test;

PART

2

单列统计

单列统计信息可帮助Planner估算一个查询将返回的行数。

统计信息保存在pg_class和pg_statistics中。pg_class基本上存储了每个表和索引中的项的总数，以及每个表和索引占用的磁盘块数。pg_statistics存储关于每列的统计信息，这些信息将用于估算WHERE字句的选择度，即符合WHERE字句中每个条件的行的比例，比如：列项为空的比例stanullfrac、非空项的平均存储宽度stawidth、第N个槽位的列值，stavaluesN等。 建议手动检查统计信息使用视图 pg_stats ，而非直接访问pg_statistic。相比之下，pg_stats可以被所有用户访问并且字段值更易读。   pg_stats列含义如下：

名称	类型	描述
schemaname	name	模式名
tablename	name	表名
attname	name	列名
inherited	bool	此行是否包括继承子列
null_frac	real	列项中为空的比例
avg_width	integer	列项的平均字节宽度
n_distinct	real	列中可区分值的估计个数
most_common_vals	anyarray	列中最常用值的一个列表
most_common_freqs	real[]	最常用值的频率列表
histogram_bounds	anyarray	将列值划分成大小接近的组的值列表
correlation	real	物理行顺序和列值逻辑顺序之间的统计关联
most_common_elems	anyarray	列值中最经常出现的非空元素列表
most_common_elem_freqs	real[]	最常用元素值的频度列表
elem_count_histogram	real[]	在列值中可区分非空元素值计数的一个直方图

通过查询pg_stats获取test表格中x列统计信息如下：

postgres=# select * from pg_stats where tablename ='test' and attname = 'x';-[ RECORD 1 ]----------+-----------------------schemaname            | publictablename             | testattname               | xinherited             | fnull_frac             | 0avg_width             | 4n_distinct            | 3most_common_vals      | {1,0,2}most_common_freqs     | {0.3334,0.3333,0.3333}histogram_bounds      |correlation           | 0.3332most_common_elems     |most_common_elem_freqs |elem_count_histogram  |

为了使查询计划易于阅读，进行设置关闭每个查询的并行性。

postgres=# set max_parallel_workers_per_gather=0;

执行如下查询语句

postgres=# EXPLAIN ANALYZE SELECT * FROM test where x=1;                                      QUERYPLAN                                             ---------------------------------------------------------------------------------------------- Seq Scan ontest  (cost=0.00..170.00 rows=3334width=8) (actual time=0.018..2.974 rows=3334 loops=1)   Filter: (x =1)   Rows Removedby Filter: 6666 Planning Time:0.144 ms Execution Time:3.438 ms(5 rows)postgres=# EXPLAIN ANALYZE SELECT * FROM test where y=1;                                     QUERYPLAN                                            -------------------------------------------------------------------------------------------- Seq Scan ontest  (cost=0.00..170.00 rows=334width=8) (actual time=0.028..4.026 rows=334 loops=1)   Filter: (y =1)   Rows Removedby Filter: 9666 Planning Time:0.063 ms Execution Time:4.115 ms(5 rows)

由于x列对3取模，所以，它的值只能是0,1,2，可以得出x=1的估计行数约为(1/3*10000)。通过结果能够看出，估计x=1的行数是3334，查询返回的实际行数是3334，非常准确。

Y列对30取模，其值是0~29，所以y=1的估计行数约为(1/30*10000)。通过结果能够看出，估计y=1的行数是334，查询返回的实际行数也是334，非常准确。

上面是条件查询中只包含一个条件的时候，那么让我们看一下，如果包含两个以上会怎么样呢？

postgres=# EXPLAIN ANALYZE SELECT * FROM test where x=1 and y=1;                                     QUERYPLAN                                            -------------------------------------------------------------------------------------------- Seq Scan ontest  (cost=0.00..195.00 rows=111width=8) (actual time=0.019..2.550 rows=334 loops=1)   Filter: ((x =1) AND (y = 1))   Rows Removedby Filter: 9666 Planning Time:0.058 ms Execution Time:2.604 ms(5 rows)

Planner估计的行数比实际行数少很多，这是由于Planner通常假定多个条件彼此独立，选择率会是每一列选择率相乘。

第一个列的选择率为 (1/3)，第二个列的选择率为 (1/30)。那么两个独立条件的选择率= (1/3) * (1/30) ≈ 0.0111。

执行计划中的估计行数为0.0111*10000得到111，这就是rows=111的来源。但是，在该例子中，x和y列并不是独立的，y的值足以能够决定x的值，所以，当这两个条件同时存在的时候，我们只考虑y的选择范围即可。解决这个问题就需要使用CREATE STATISTICS来创建扩展统计的对象。

PART

3

扩展统计

有时候会看到查询缓慢，运行了错误的执行计划，很多是因为查询子句中包含多列。为此，PostgreSQL提供了扩展统计，可以创建扩展统计信息对象，也能解决多个非均匀分布列的查询，以指示服务器获得统计信息和更好计划。

使用CREATE STATISTICS创建统计信息对象，只是创建一个表示对统计信息相关的目录条目。实际的数据收集由ANALYZE执行(手动命令或后端自动分析)，ANALYZE根据计算常规单列统计信息所用的相同表行样本来计算扩展统计信息，可以在pg_statistic_ext中检查收集的值。

pg_statistic_ext列包含：

名称	类型	描述
stxrelid	oid	包含此对象描述的列的表
stxname	name	统计对象的名称
stxnamespace	oid	包含此统计信息对象的名称空间的OID
stxowner	oid	统计对象的所有者
stxkeys	int2vector	统计对象覆盖的属性编号数组
stxkind	char[]	包含已启用统计类型代码的数组
stxndistinct	pg_ndistinct	N-distinct计数
stxdependencies	pg_dependencies	函数依赖性统计信息

以下小节介绍当前支持的扩展统计信息的种类。

3. 1

函数依赖统计

最简单的扩展统计是函数依赖统计。如果知道a的值足够确定b的值，那就说明列b依赖于列a，即不存在两行有相同的a值但不同的b值。

函数依赖的存在直接影响某些查询中估计的准确性。为了向Planner通知函数依赖关系，ANALYZE可以收集跨列依赖关系的度量。此处我们建议仅为高度相关的列组件依赖统计信息，以避免在ANALYZE和以后的查询计划中产生不必要的开销。

回到我们单列统计的问题，x的值仅仅是y的1/10，所以x是函数依赖于y，也就是说，y的值足以决定x的值，不存在两行数据有相同的y但有不同的x。因此，在x列上的过滤筛选并没有移除任何行，我们创建一个统计对象去捕获这些列和运行分析(ANALYZE)所依赖的函数统计。

postgres=# CREATE STATISTICS depstats (dependencies)on x,y from test;CREATE STATISTICSpostgres=# ANALYZE test;ANALYZE

让我们来看一下，执行如下语句时会发生什么？

postgres=# EXPLAIN ANALYZE SELECT * FROM test where x=1 and y=1;                                     QUERYPLAN                                            -------------------------------------------------------------------------------------------- Seq Scan ontest  (cost=0.00..195.00 rows=334width=8) (actual time=0.017..2.822 rows=334 loops=1)   Filter: ((x =1) AND (y = 1))   Rows Removedby Filter: 9666 Planning Time:0.173 ms Execution Time:2.880 ms(5 rows)

估算的行数为334，实际返回的也是334。估算非常准确。这里的计算和单列统计是完全不同的，这里估算的行数是完全由y决定的。   让我们看一下对计划的测量

postgres=# SELECT stxname, stxkeys, stxddependenciesFROM pg_statistic_ext join pg_statistic_ext_data on(oid = stxoid)WHERE stxname = 'depstats'; stxname  | stxkeys |  stxddependencies   ----------+---------+---------------------- depstats | 12     | {"2 => 1": 1.000000}(1 row)

从结果可以看出，统计信息对象覆盖了第一列和第二列(即x列和y列)，PG通过函数依赖性信息意识到第二列y完全决定第一列x，因此用条件y来捕获这些信息，提高了评估的准确性。 3.2

N-Distinct统计

Planner可以获取每列不同值的统计数字，但再次合并多列时(例如：group by x,y)，估算偏差太大，无法选择一个好的计划。

postgres=# EXPLAIN ANALYZE SELECT x,y,count(*) fromtest group by x, y;                                  QUERYPLAN                                                 ---------------------------------------------------------------------------------------------- HashAggregate (cost=220.00..220.90 rows=90 width=16) (actual time=9.548..9.560 rows=30loops=1)   Group Key: x,y   ->  Seq Scan on test  (cost=0.00..145.00 rows=10000 width=8)(actual time=0.023..2.731 rows=10000 loops=1) Planning Time:0.100 ms Execution Time:9.607 ms(5 rows)

可以看到，这种情况下，planner估算出来的行数(等于x、y的不同值的数量)为90，但实际返回行数仅为30，导致估算偏差较大。 为了改进这种估计，ANALYZE可以为列组收集N个不同的统计数据。与以前一样，对每个可能的列组都进行此操作是不切实际的，因此仅针对那些出现在由N-Distinct选项定义的统计信息对象中的列组收集数据。将针对列出的列中的每个可能组合收集数据。   让Planner去捕获N-Distinct统计信息，重新运行查询并找出结果。

postgres=# CREATE STATISTICS disstats(ndistinct) on x,y from test;CREATE STATISTICSpostgres=# ANALYZE test;ANALYZE

执行如下聚合查询语句

postgres=# EXPLAIN ANALYZE SELECT x,y,count(*) fromtest group by x, y;                                          QUERYPLAN                                                 ---------------------------------------------------------------------------------------------- HashAggregate (cost=220.00..220.30 rows=30 width=16) (actual time=9.287..9.304 rows=30loops=1)   Group Key: x,y   ->  Seq Scan on test  (cost=0.00..145.00 rows=10000 width=8)(actual time=0.015..2.562 rows=10000 loops=1) Planning Time:0.133 ms Execution Time:9.345 ms(5 rows)

让我们看一下对计划的测量

postgres=# SELECT stxname, stxkeys, stxdndistinct  FROM pg_statistic_ext joinpg_statistic_ext_data on (oid = stxoid) WHERE stxname = 'disstats'; stxname  | stxkeys | stxdndistinct----------+---------+--------------- disstats | 12     | {"1, 2": 30}(1 row)

从结果可以看出，统计信息对象覆盖了第一列和第二列(即x列和y列)，PG通过N-Distinct统计信息意识到第一列和第二列有30个不同的列组。可以看到，现在的估算精度更高了。 3.3

MCV Lists统计

函数依赖统计是一种节约且高效的统计类型，但它的局限性在于列之间必须具备依赖性，对于独立的列无法解决。 MCV(most common values) lists统计是PG12新增的功能，允许对多列进行最常用值统计，由ANALYZE收集列组合上的MCV列表，并存储了公共列值. 与其他统计方式不同，MCV Lists统计需要使用pg_mcv_list_items函数来检查MCV内容，它可以返回多列MCV列表中存储的所有项目的列表，包含以下列：

名称	类型	描述
index	int	MCV列表中项目的索引
values	text[]	存储在MCV项目中的值
nulls	boolean[]	标识NULL值的标志
frequency	double precision	此MCV项目的频率
base_frequency	double precision	此MCV项目的基本频率

3.3.1

缺点

MCV lists统计无论从ANALYZE、存储和plan时间方面都相对比较耗费资源。   下面我们通过实例来对比一下

postgres=# DROP STATISTICS depstats;DROP STATISTICSpostgres=# DROP STATISTICS disstats;DROP STATISTICSpostgres=# CREATE STATISTICS mcvstats(mcv) ON x,y FROMtest;CREATE STATISTICSpostgres=# ANALYZE test;ANALYZE

为了确保是MCV Lists统计起的作用，我们将之前创建的statistics删除后，在创建新的统计对象。 在此基础上，执行如下语句：

postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE x= 1 AND y = 1;                                            QUERY PLAN                                            -------------------------------------------------------------------------------------------- Seq Scan ontest  (cost=0.00..195.00 rows=334width=8) (actual time=0.032..3.729 rows=334 loops=1)   Filter: ((x =1) AND (y = 1))   Rows Removedby Filter: 9666 Planning Time:0.296 ms Execution Time:3.814 ms(5 rows)

我们可以看到准确率和函数依赖统计是一样的，但是plan时间要长一些，同时，ANALYZE和存储也比较耗费资源。

3.3.2

优点

1、MCV列表中存储的是真实的值，能够判断一个组合是否包含

MCV List方式

postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE x= 1 AND y = 2;                                          QUERY PLAN                                          ---------------------------------------------------------------------------------------------- Seq Scan ontest  (cost=0.00..195.00 rows=1 width=8)(actual time=3.354..3.354 rows=0 loops=1)   Filter: ((x =1) AND (y = 2))   Rows Removedby Filter: 10000 Planning Time:0.085 ms Execution Time:3.371 ms(5 rows)

函数依赖方式

postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE x= 1 AND y = 2;                                           QUERY PLAN                                           ---------------------------------------------------------------------------------------------- Seq Scan ontest  (cost=0.00..195.00 rows=334width=8) (actual time=2.966..2.966 rows=0 loops=1)   Filter: ((x =1) AND (y = 2))   Rows Removedby Filter: 10000 Planning Time:0.102 ms Execution Time:2.981 ms(5 rows)

对于MCV Lists统计方式，鉴于列表中存储的是真实的值，x=1且y=2的组合是不存在列表中，能够立刻判断并迅速响应。预估行数为1，并且Planning的时间较短。 函数依赖统计方式的额估算行数值为334，且Planning的时间也要长一些。   2、MCV列表能够处理条件语句是范围的情况，而非函数依赖统计的条件语句必须是等于的情况

postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE x> 3 AND y < 0;                                          QUERY PLAN                                          ---------------------------------------------------------------------------------------------- Seq Scan ontest  (cost=0.00..195.00 rows=1 width=8)(actual time=3.177..3.177 rows=0 loops=1)   Filter: ((x> 3) AND (y < 0))   Rows Removedby Filter: 10000 Planning Time:0.077 ms Execution Time:3.194 ms(5 rows)

pg_mcv_list值只能从pg_statistic_ext_data.stxdmcv列中获得. pg_mcv_list_items函数按照如下方式使用：

postgres=# SELECT m.* FROM pg_statistic_ext joinpg_statistic_ext_data on (oid = stxoid), pg_mcv_list_items(stxdmcv) m WHEREstxname = 'mcvstats'; index |values  | nulls | frequency |base_frequency-------+---------+-------+-----------+----------------     0 | {2,2}  | {f,f} |    0.0334 |       0.011132     1 | {2,5}  | {f,f} |    0.0334 |       0.011132     2 | {2,8}  | {f,f} |    0.0334 |       0.011132     3 | {1, 10}| {f,f} |    0.0334 |       0.011136     4 | {0,3}  | {f,f} |    0.0334 |       0.011132     5 | {0,6}  | {f,f} |    0.0334 |       0.011132     6 | {0,9}  | {f,f} |    0.0334 |       0.011132     7 | {1,1}  | {f,f} |    0.0334 |       0.011136     8 | {1,4}  | {f,f} |    0.0334 |       0.011136     9 | {1,7}  | {f,f} |    0.0334 |       0.011136    10 | {1, 22}| {f,f} |    0.0333 |       0.011102    11 | {1, 25}| {f,f} |    0.0333 |       0.011102    12 | {1, 28}| {f,f} |    0.0333 |       0.011102    13 | {2, 11}| {f,f} |    0.0333 |       0.011099    14 | {2, 14}| {f,f} |    0.0333 |       0.011099    15 | {2, 17}| {f,f} |    0.0333 |       0.011099    16 | {2, 20}| {f,f} |    0.0333 |       0.011099    17 | {2, 23}| {f,f} |    0.0333 |       0.011099    18 | {2, 26}| {f,f} |    0.0333 |       0.011099    19 | {0,0}  | {f,f} |    0.0333 |       0.011099    20 | {2, 29}| {f,f} |    0.0333 |       0.011099    21 | {0, 12}| {f,f} |    0.0333 |       0.011099    22 | {0, 15}| {f,f} |    0.0333 |       0.011099    23 | {0, 18}| {f,f} |    0.0333 |       0.011099    24 | {0, 21}| {f,f} |    0.0333 |       0.011099    25 | {0, 24}| {f,f} |    0.0333 |       0.011099    26 | {0, 27}| {f,f} |    0.0333 |       0.011099    27 | {1, 13}| {f,f} |    0.0333 |       0.011102    28 | {1, 16}| {f,f} |    0.0333 |       0.011102    29 | {1, 19}| {f,f} |    0.0333 |       0.011102(30 rows)

通过查询能够看出，这两列一共有30个不同的组合。频率是当前组合的频率，这个例子中所有组合的可能性大致相同，每一种频率为1/30≈0.033；基本频率是根据每列统计信息计算的频率，即(1/3)*(1/30)≈0.011 I Love PG

关于我们

中国开源软件推进联盟PostgreSQL分会(简称：PG分会)于2017年成立，由国内多家PG生态企业所共同发起，业务上接受工信部产业发展研究院指导。PG分会致力于构建PG产业生态，推动PG产学研用发展，是国内的一家PG行业协会组织。

欢迎投稿

做你的舞台，show出自己的才华 。

投稿邮箱：partner@postgresqlchina.com

                                 ——愿能安放你不羁的灵魂

技术文章 精彩 回顾PostgreSQL学习的九层宝塔PostgreSQL职业发展与学习攻略搞懂PostgreSQL数据库透明数据加密之加密算法介绍一文读懂PostgreSQL-12分区表PostgreSQL源码学习之：RegularLockPostgresql源码学习之词法和语法分析PostgreSQL buffer管理最佳实践—PG数据库系统表空间重建PostgreSQL V12中的流复制配置2019，年度数据库舍 PostgreSQL 其谁?PostgreSQL使用分片(sharding)实现水平可扩展性一文搞懂PostgreSQL物化视图PostgreSQL原理解析之：PostgreSQL备机是否做checkpointPostgreSQL复制技术概述 PG活动 精彩 回顾见证精彩|PostgresConf.CN2019大会盛大开幕PostgresConf.CN2019大会DAY2|三大分论坛，精彩不断PostgresConf.CN2019培训日|爆满！Training Day现场速递！「PCC-Training Day」培训日Day2圆满结束，PCC2019完美收官创建PG全球生态！PostgresConf.CN2019大会盛大召开首站起航！2019“让PG‘象’前行”上海站成功举行走进蓉城丨2019“让PG‘象’前行”成都站成功举行中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行PostgreSQL实训基地落户沈阳航空航天大学和渤海大学，高校数据库课改正当时群英论道聚北京，共话PostgreSQL相聚巴厘岛| PG Conf.Asia 2019  DAY0、DAY1简报相知巴厘岛| PG Conf.Asia 2019 DAY2简报相惜巴厘岛| PG Conf.Asia 2019 DAY3简报独家|硅谷Postgres大会简报全球规模最大的PostgreSQL会议等你来！ PG培训认证 精彩 回顾关于中国PostgreSQL培训认证，你想知道的都在这里！首批中国PGCA培训圆满结束，首批认证考试将于10月18日和20日举行！中国首批PGCA认证考试圆满结束，203位考生成功获得认证！中国第二批PGCA认证考试圆满结束，115位考生喜获认证！请查收：中国首批PGCA证书！重要通知：三方共建，中国PostgreSQL认证权威升级！一场考试迎新年 | 12月28日，首次PGCE中级认证考试开考！近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！