Postgresql统计信息概述

 

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

开始之前，有必要了解一下postgresql的进程结构。
postgresql 进程模型，与MySQL或者SQLServer的单进程多线程机制不同，Postgresql为多进程模型，每个进负责特定的任务，同时每个进程自身有各自的运行或者唤醒机制。 相比单进程多线程的MySQL，各个子线程寄宿与主进程中的方式相比，postgresql这一点可以直观地来描述每个进程以及其作用。 如下是一个Postgresql实例的所有进程信息，这里仅关注autovacuum lancher和stat collector，后文会提交到这两个进程。 另：MySQL的单进程多线程服务模型，注：mysql_safe进程仅仅起到mysqld进程carsh后进程唤醒的作用，其他后台功能以线程的形式寄宿在mysqld主进程。
SQLServer的进程就太熟悉了，默认进程名称就是实例名，默认MSSQL 在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表以及测试数据的生成

create table myschema.table_test
(
    c1 serial  primary key,
    c2 int,
    c3 varchar(100),
    c4 varchar(100),
    c5 timestamp
)

create or replace function random_string(integer)
returns text as
$body$
    select upper(array_to_string(array(select substring("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" FROM (ceil(random()*62))::int FOR 1) FROM generate_series(1, $1)), ""));
$body$
language sql volatile;

insert into myschema.table_test (c2,c3,c4,c5)
select cast(random()*500000 as int),random_string(10),random_string(10), cast( now()-"1 min"::interval * random()*500000  as timestamp )
from generate_series(1,1000000)

• 库级别信息摘要

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

• 表级别的信息摘要

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

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

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-05 14:29:35.515329","2019-08-08 19:17:14.628418",……"2020-07-14 18:25:47.515329","2020-07-17 19: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系统表的详细描述如下

  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