Postgresql统计信息概述

  • Z时代
  • 2024-01-10
  • 分类：综合

database

 

对于sql优化，除了索引之外，执行计划和统计信息是无法绕开的一个话题，如果sql优化（所有的RDBMS）脱离了统计信息的话就少了一个为什么的过程，味道就感觉少了一大半。
刚接触Postgresql，粗浅地学习总结一下Postgresql相关的统计信息。

postgresql 进程模型

开始之前，有必要了解一下postgresql的进程结构。

postgresql 进程模型，与MySQL或者SQLServer的单进程多线程机制不同，Postgresql为多进程模型，每个进负责特定的任务，同时每个进程自身有各自的运行或者唤醒机制。

相比单进程多线程的MySQL，各个子线程寄宿与主进程中的方式相比，postgresql这一点可以直观地来描述每个进程以及其作用。

如下是一个Postgresql实例的所有进程信息，这里仅关注autovacuum lancher和stat collector，后文会提交到这两个进程。

另：MySQL的单进程多线程服务模型，注：mysql_safe进程仅仅起到mysqld进程carsh后进程唤醒的作用，其他后台功能以线程的形式寄宿在mysqld主进程。

SQLServer的进程就太熟悉了，默认进程名称就是实例名，默认MSSQL

postgresql中的两种“统计信息”

在MySQL或者SQLServer中，统计信息这个术语是描述数据分布状态的一种信息，由后台线程或者人为命令触发更新。

与SQLServer或者MySQL中的一些术语不同，postgresql中的stats分为两类如下两类

1，第一类是类似于SQLServer或者MySQL中的性能计数器和系统表，这些数据用来描述数据库的负载，或者数据库对象使用情况，
       SQLServer的性能计数器和各种系统表和DMV，或者MySQL中的performance_schema中的的信息

2，第二类是类似于SQLServer或者MySQL中的统计信息直方图，用来描述数据的分布，为优化器生成执行计划提供依据。
    这一类统计信息基本上等价于SQLServer或者MySQL中用来指导执行计划的统计信息

    “统计信息”在Postgresql中的含义，与SQLServer或者MySQL相比，第一类信息的称呼上存在一些不同。
参考这里：https://blog.pgaddict.com/posts/the-two-kinds-of-stats-in-postgresql

  • postgresql中的负载指标“统计信息”（Monitoring stats）

负载指标由上文提到的stats collector进程来实时收集更新。PostgreSQL的统计数据收集器是一个支持收集和报告服务器活动信息的子系统。
目前，收集器可以计算对磁盘块和单行项中的表和索引的访问次数。
它还跟踪每个表中的总行数，以及关于vacuum的信息，并分析每个表的操作。
同时还可以记录基于sql语句执行的代价信息。这部分与该主题关系不大，就不展开详述。

  • postgresql中的数据分布状态描述“统计信息”（Data distribution stats）

对于第二类统计信息，也即用来描述数据分析状态的信息，有两种生成方式

1，后台进程autovacuum lancher会在特定的情况下触发统计信息的更新，这一点类似于SQLServer统计信息的自动更新机制。

2，手动执行analyze table，这一点类似于在SQLServer中手动执行统计信息更新。
　比如update statistics  *** (***) with sample 30 percent，或者MySQL中的analyze table  table_name

　此外，基于postgresql的MVCC机制生成的“非活动数据”，更新可见性映射，冻结事务处理（保护老旧数据不会由于事务ID回卷或多事务ID回卷而丢失），也是由autovacuum 进程来清理，同时，还负责XID的清理工作。

有两种VACUUM的变体：标准VACUUM和VACUUM FULL。
1，标准形式的VACUUM可以和生产数据库操作并行运行（SELECT、INSERT、UPDATE和DELETE等命令将继续正常工作，但在清理期间你无法使用ALTER TABLE等命令来更新表的定义）。
2，VACUUM FULL可以收回更多磁盘空间但是运行起来更慢，且vacuum full不会有后台进程主从触发（只能手动执行）。
另外，VACUUM FULL类似于表的重建或者说碎片整理，同时需要一个大小相当于原始表的额外空间。
要求在其工作的表上得到一个排他锁，因此无法和对此表的其他使用并行。因此，通常管理员应该努力使用标准VACUUM并且避免VACUUM FULL。

以上信息作为铺垫，以下简单概述postgresql中的“统计信息”，所谓的两种统计信息区别其实在含义上非常容易理解，同时会涉及两种统计信息的系统表。
大概列举一下相关的系统表以及相关参数，简述相关的含义。

 

如下为测试demo表以及测试数据的生成

createtable myschema.table_test
(
    c1 serial  primarykey,
    c2 int,
    c3 varchar(100),
    c4 varchar(100),
    c5 timestamp
)
createorreplacefunction random_string(integer)
returnstextas
$body$
selectupper(array_to_string(array(selectsubstring("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"FROM (ceil(random()*62))::intFOR1) FROM generate_series(1, $1)), ""));
$body$
language sql volatile;
insertinto myschema.table_test (c2,c3,c4,c5)
selectcast(random()*500000asint),random_string(10),random_string(10), cast( now()-"1 min"::interval * random()*500000astimestamp )
from generate_series(1,1000000)

postgresql中关于数据库负载的统计信息

  • 库级别信息摘要

在库级别，pg_stat_databases用来描述描述"库"级别的摘要信息，包括库名，当前库事务提交次数，回滚次数，读写次数，死锁等等信息。
这些信息可以观察到到一个库的负载情况和健康状况。

  • 表级别的信息摘要

在表级别，由pg_stat_user_tables来描述某个具体的表中的信息，包括增删查改的次数，数据行等摘要信息。
这些信息可以衡量一个表的冷热程度，活跃性，以及体量以及一些analyze时间相关的信息。

pg_class 来描述表的物理存储信息，包括数据行数，数据页的个数。

关于postgresql中的描述数据分布统计信息

pg_stats用来描述一个表中所有的字段的数据分布信息，为执行计划决策提供依据，与SQLServer的直方图类似，熟悉的配方熟悉的味道，只有管理员账号能够访问
类似于SQLServer的统计信息+直方图，也即执行dbcc show_statistics(***,***)的效果，或者MySQL中的information_schema.column_statistics表中的信息（简直一模一样）。
这一点，SQLServer的直方图用一种相对比较直观的方式展示了出来。

select *from pg_stats   WHERE tablename ="table_test";
schemaname             | myschema
tablename              | table_test
attname                | c1
inherited              | f
null_frac              |0
avg_width              |4
n_distinct             |-1
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {15,9799,20037,30372,40276,……………………,990687,999949}
correlation            |1
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |
schemaname             | myschema
tablename              | table_test
attname                | c2
inherited              | f
null_frac              |0
avg_width              |4
n_distinct             |-0.330106
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {23,4712,9677,14189,19403,………………490576,495541,499975}
correlation            |-0.00480835
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |
schemaname             | myschema
tablename              | table_test
attname                | c5
inherited              | f
null_frac              |0
avg_width              |8
n_distinct             |-0.993476
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {"2019-08-0514:29:35.515329","2019-08-0819:17:14.628418",……"2020-07-1418:25:47.515329","2020-07-1719:40:48.015329"}
correlation            |-0.00216757
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |
schemaname             | myschema
tablename              | table_test
attname                | c4
inherited              | f
null_frac              |0
avg_width              |11
n_distinct             |-1
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {0035UXPI6A,0N8JC5OIER,1BZZAU76H5,…………ZQSMJJRFWE,ZZZYYV9TKJ}
correlation            |-0.00186405
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

schemaname             | myschema
tablename              | table_test
attname                | c3
inherited              | f
null_frac              |0
avg_width              |11
n_distinct             |-1
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {000XXEZ4HN,0N3GEAC1QS,…………,Z5ANIIBHDO,ZH6ZYR94CJ,ZQW7M2HZ4I,ZZZENAC3OQ}
correlation            |0.00391295
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |
Time: 1.259 ms

关于pg_stat系统表的详细描述如下

名字

类型

引用

描述

schemaname

name

pg_namespace.nspname

包含此表的模式名字

tablename

name

pg_class.relname

表的名字

attname

name

pg_attribute.attname

这一行描述的字段的名字

inherited

bool

 

如果为真，那么这行包含继承的子字段，不只是指定表的值。

null_frac

real

 

记录中字段为空的百分比

avg_width

integer

 

字段记录以字节记的平均宽度

n_distinct

real

 

如果大于零，就是在字段中独立数值的估计数目。如果小于零， 就是独立数值的数目被行数除的负数。
用负数形式是因为ANALYZE

认为独立数值的数目是随着表增长而增长；
正数的形式用于在字段看上去好像有固定的可能值数目的情况下。比如， -1

表示一个唯一字段，独立数值的个数和行数相同。

most_common_vals

anyarray

 

一个字段里最常用数值的列表。如果看上去没有啥数值比其它更常见，则为 null

most_common_freqs

real[]

 

一个最常用数值的频率的列表，也就是说，每个出现的次数除以行数。 如果most_common_vals是 null ，则为 null。

histogram_bounds

anyarray

 

一个数值的列表，它把字段的数值分成几组大致相同热门的组。 如果在most_common_vals里有数值，则在这个饼图的计算中省略。


 如果字段数据类型没有<操作符或者most_common_vals 列表代表了整个分布性，则这个字段为 null。

correlation

real

 

统计与字段值的物理行序和逻辑行序有关。它的范围从 -1 到 +1 。 在数值接近 -1 或者 +1

的时候，在字段上的索引扫描将被认为比它接近零的时候开销更少， 因为减少了对磁盘的随机访问。
如果字段数据类型没有<操作符，那么这个字段为null。

most_common_elems

anyarray

 

经常在字段值中出现的非空元素值的列表。（标量类型为空。）

most_common_elem_freqs

real[]

 

最常见元素值的频率列表，也就是，至少包含一个给定值的实例的行的分数。 每个元素频率跟着两到三个附加的值；它们是在每个元素频率之前的最小和最大值，

还有可选择的null元素的频率。
当most_common_elems 为null时，为null）

elem_count_histogram

real[]

 

该字段中值的不同非空元素值的统计直方图，跟着不同非空元素的平均值。（标量类型为空。）

 

pg_statistic是基于pg_stats的视图，以更加友好以及可读的方式展现统计信息，普通用户可以访问

统计信息的更新

1，统计信息的手动更新：analyze table_name

 　2，统计信息的自动更新

开始之前，对比SQLServer和MySQL中统计信息的自动更新的出发情况，统计信息更新是一个非常有意思的话题。
SQLServer是表中的输入写入（增删改）超过阈值500 + (20 %×表数据总量)之后会自动触发更新，以为默认情况下可以认为这是一个写死的参数。
因为SQLServer统计信息的更新会有非常多的问题，虽然SQLServer有一个trace flag 2371
可以改变改规则，但也属于半遮半掩的一个非开放功能对于MySQL或者postgresql，类似所有的参数都是可配置化的，因此非常透明
MySQL是innodb_stats_auto_recalc打开的情况下，增删改的次数大于表中已有数据的10%之后主动触发更新。

2.1 自动更新的开关
首先autovacuum开关需要打开，也即上文中提到的autovacuum lancher进程实现，在表中的数据满足一定条件之后的定时更新　　
这里的autovacuum是这个自动化更新的开关。默认打开。

2.2关于自动更新的触发机制
也类似于MySQL，子线程会根据上下文，存在一个工作频率，postgresql在打开autovacuum基础上，
autovacuum进程执行统计信息更新的唤醒频率，以及工作线程数，依次对各个表执行并发清理，
autovacuum_naptime唤醒频率默认为1min，autovacuum_max_workers工作线程默认为3个，被唤醒的工作线程会并发对库中的满足更新条件的表进行统计信息更新。

2.3关于自动更新统计信息的阈值
这里会涉及两个参数autovacuum_vacuum_threshold和autovacuum_vacuum_scale_factor
anl_base_thresh默认值时50，anl_scale_factor默认值时0.2，这都是可配置的，而且是每个表可以独立配置的，这里难免会想到SqlServer的这个阈值也是类似变化数量超过500 + (20 %×表数据总量)
autovacuum进程进行 vacuum 触发条件表上增删改的行数 >= autovacuum_vacuum_scale_factor* reltuples(表上记录数) + autovacuum_vacuum_threshold

 　　2.4关于自动更新的采样范围

　　这里涉及一个default_statistics_target参数，该参数默认值是100.范围是1~10000。
　　参考这里，提了一个非常好的问题：https://dba.stackexchange.com/questions/200136/postgresql-what-the-default-statistics-target-value-really-means
　　采样容量：采用300×default_statistics_target=30000作为采样的样本默认容量，default_statistics_target是一个可配置化的参数，默认值变为100
　　这一点类似于MySQL中的STATS_SAMPLE_PAGES，也就是更新统计信息时候采样的比例，只不过这里是page页面的个数，也是可以基于表来配置的。
　　难免又想到Server的类似配置项，SqlServer没有该配置项，不过一切都不是问题，可以换种方式间接使实现。
　　多扯一句，很多人在讲什么SQL优化，执行计划，索引之类的，如果抛开统计信息不谈，基本上还是那种初级的手段，或者根本就没遇到过复杂的场景。
　　上面说了，对于采样容量，300×default_statistics_target=30000作为采样的样本默认容量，default_statistics_target可配置化，这一切看起来都没有问题，其实可以仔细考虑一下。
　　对于某些大表或者数据分布不均匀的请，300×default_statistics_target是不一定够的，对于10W行的表，采用30000的采样容量，跟1000W行的表采用同样的容量，统计信息得到的数据准确性差别是非常大的。

　　因此，如何根据具体的表，具体的索引结构，具体的应用场景，调整default_statistics_target这个变量就是非常有必要的了。
　　上面说了，MySQL和SQLServer可以在表级别定义一个STATS_SAMPLE_PAGES或者default_statistics_target的取样范围。
　　sqlserver有一个类似于与此的统计采样百分比，update statistics  *** (***) with sample 30 percent，就是这里的30，这个值可以从0~100%，随意指定。
　　可见，很多东西是殊途同归的，只是外面的一层马甲不一样而已。（为什么往往要从一定范围内采样而不是100%采样？其实脚后跟就能想明白）

　　

　　statistics采样比例是一个可以在列级别指定的参数，范围是0~1000，语法是：Alter table <table_name> alter column <column_name> set statistics < value from 1 to 1000 > ;

　　
　　这个字段级统计信息的属性记录在pg_attribute这个系统表中。

　　

2.5 统计信息最后一次更新之后的变化
统计信息更新日志，pg_stat_all_tables表存储了所有表的最后一次更新历史信息（last_analyza），以及最后一次更新之后数据发生的变化情况(n_mod_since_analyze)，这是一个非常因吹斯汀的数据。
上面把统计信息各种阈值，各种触发条件七七八八地列举的差不多了，有没有表再回头关注最后一次统计信息更新之后表的基数的变化？
肯定是有必要的，上面说了，即便是default_statistics_target是一个可以调整的参数，但不一定知道具体哪个值是合理的或者说是可行的。
那么，就可以观察，在执行计划使用统计信息做预估，出现偏差的临界点，就需要重新收集更新统计信息了，
那么此时就可以结合pg_stat_all_tables此时举上次收集完统计信息变化的情况，来反推autovacuum_vacuum_scale_factor这个值，从而更加科学地去设置autovacuum_vacuum_scale_factor这个因子。

　3 手工创建统计信息以及多列统计信息

开始之前现提出一个问题：为什么需要手动创建统计信息？
通常情况下，统计信息在满足一定条件，且取样达到一定程度之后，可以得到一个相对准确的统计信息，一切看起来都是水到渠成。
但是不排除一些个特殊情况，需要手动创建统计信息来实现预估的准确性，比如数据倾斜严重的情况下，又难以100%取样（即便100%取样，统计信息还有一个“步长”的限制），
此时手动创建统计信息，从而更好指导执行计划的生成。
这一点SQLServer和Postgresql都是支持的，MySQL这一点是不支持的。
这个就稍微扯远一点，SQLServer中对于select * from table where c1 = m and c2 = n这种语句，返回行数是如何预估的？
如之前提到过的，假如c1的选择性为p1,c2的选择性为p2，表中的总行数为table_rowcount，暂忽略索引自身以及统计信息准确性带来的影响，以此为前提。
在SQLServer 2012中是预估返回函数是p1*p2*table_rowcount，
在SQLServer 2014或者更高版本中，这个算法发生了变化，是P1*P21/2*table_rowcount
简单demo一下，假设在c2和c3字段上某些条件值分布的特别不均匀（严重倾斜）的情况下，创建这么一个统计信息之后，可以指导执行计划在遇到类似的查询条件之后，做出更加准确的预估。

以上同理，Postgresql中也可以创建类似的统计信息，但又不完全相同，postgresql 更强调的是两个或多个字段之间的关联性。

对于预估的返回行数是会直接影响执行计划的，这个结果是比较重要的，但是SQLServer中不管是第一种算法或者是第二种算法，都是基于一种“猜测”的方式，而没有具体依赖于数据的关联性。

那么，Postgresql恰恰就改进了这一点

demo如下

CREATE statistics if not exists statis_c2_c5 (dependencies) ON c2, c5 FROM myschema.table_test;

系统表pg_statistic_ext中存放了改扩展统计信息。

对于多列（多个字段）统计信息，会计算列之间的依赖度来生成统计信息，而非SQLServer的p1*p2*table_rowcount或者P1*P21/2*table_rowcount等“猜测”的方式。
详细信息参考笔者的上一篇译文：https://www.cnblogs.com/wy123/p/13306673.html
需要注意的是，在创建完统计信息之后，且执行analyze table，且指定的列数与统计信息中的一致的时候，才会生成该统计信息。

db01=# dropstatisticsifexists statis_c2_c5;
DROPSTATISTICS
db01=# createstatisticsifnotexists statis_c2_c5(dependencies)on c2,c5 from myschema.table_test;
CREATESTATISTICS
db01=# createstatisticsifnotexists statis_c2_c5(dependencies)on c2,c5 from myschema.table_test;
NOTICE:  statistics object "statis_c2_c5" already exists, skipping
CREATESTATISTICS
db01=# analyze myschema.table_test(c2,c5);
ANALYZE
db01=# select*from pg_statistic_ext where stxname like"%statis_c2_c5%";
-[ RECORD 1 ]---+-----------------------------------------
stxrelid        |18730
stxname         | statis_c2_c5
stxnamespace    |2200
stxowner        |10
stxkeys         |25
stxkind         | {f}
stxndistinct    |
stxdependencies | {"2=>5": 0.817946, "5=>2": 0.998274}

当然这个多列统计信息之间的依赖性，涉及的东西太多，如果准确地计算相关列之间的依赖(stdkind的f算法)是一个难题，
这其中的算法到底是如何实现的，原理是什么，计算出来结果的准确性如何等等一系列问题，笔者目前也尚不清楚，需要进一步的挖掘（备注：20200722已放弃，理由如下）。


20200722更新：
因为统计信息作为关系数据库系统中最复杂的组件之一，看到相关系度的计算原理和公式之后，就开始想念我的高数老师……,神似贝叶斯定理？？？
感慨一下，在非关系数据库中，相比关系数据是是没有也不需要如此复杂的组件的，因此再在场景下，简单就是美。

已经开始放弃原理的探索了……，开始相信实现这部分功能的人。

 

Vacuum和Analyze

统计信息的更新由vacuum进程实现，也可以有手动analyze实现，那么，Vacuum和Analyze的区别是什么？

autovcuum lancher进程中会定期的执行autovcuum ，Analyze是autovcuum其中的一步，会主动被触发。

Vacuum是Analyze的超集，Vacuum包含一系列的清理、表的重建、以及表的统计信息更新，换句话说就是，vacuum包含但不限于analyze table来更新统计信息。

参考这里：Using ANALYZE to optimize PostgreSQL queries

Vacuuming isn"t the only periodic maintenance your database needs. You also need to analyze the database so that the query planner has table statistics it can use when deciding how to execute a query. Simply put: Make sure you"re running ANALYZE frequently enough, preferably via autovacuum. And increase the default_statistics_target (in postgresql.conf) to 100.

 


参考链接：

http://www.postgres.cn/docs/9.4/monitoring-stats.html
https://blog.pgaddict.com/posts/the-two-kinds-of-stats-in-postgresql
http://www.postgres.cn/docs/10/routine-vacuuming.html
https://www.postgresql.org/docs/12/planner-stats.html
https://www.postgresql.org/docs/11/runtime-config-query.html
https://pganalyze.com/blog/postgresql-log-monitoring-101-deadlocks-checkpoints-blocked-queries
https://www.cybertec-postgresql.com/en/setting-postgresql-configuration-parameters/
https://coderbook.com/@marcus/postgres-autovacuum-vacuum-and-analyze-explained/
https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT

以上是 Postgresql统计信息概述 的全部内容， 来源链接： utcz.com/z/534759.html

回到顶部