PostgreSQL 统计信息

原创 Maleah 2022-12-13

2902

针对于 PostgreSQL 中的统计信息有些迷惑，稍微查阅了一些资料，在此分享下，本人拙见，有不妥的还请指正

在 PostgreSQL 数据库，统计信息可以大致分为两种。一种是通常意义上规划器用于生成执行计划的关于数据分布的统计信息，还有一种是跟踪服务进程的统计信息

一、数据分布统计信息

1、什么是数据分布统计信息？

与每张表的数据分布有关，是一种描述数据分布的统计数据。

规划器使用的统计信息有两种：

单列统计信息

比较常见的，可以从pg_statistic表中查看某张表单个字段的统计信息
扩展统计信息

analyze只能收集表的单列统计信息，对于查询子句中用到多个列相关联的情况，可能会由于缺乏相对应的统计信息而导致走了错误的执行计划的慢查询产生。针对常规的统计信息无法捕捉到跨列关联，可以创建扩展统计信息（CREATE EXTENSION）来指示服务器获得相关的列集合的统计信息，实现自动计算多元统计信息

2、有什么作用？

数据库在执行一条SQL时可以有多种执行方式，例如顺序扫描、索引扫描等等，那么数据库要选择哪一种方式来生成执行计划呢？这就要用到统计信息。规划器可以通过收集的统计信息为查询语句选择一个最优的方式来生成执行计划

（所以统计信息的更新不及时也可能导致统计信息的不准确，进而影响规划器生成的执行计划，从而可能会导致走错误执行计划的慢查询）

3、谁收集的？

vacuum
analyze

4、保存在哪？

1）单列统计信息

pg_class.reltuples、pg_class.relpages

记录表的tuple的行数和page页数
pg_statistic 系统表（由于pg_statistic 系统表中的数据人为不易阅读，一般从pg_stats 系统视图查看）

记录表的单个字段的值的分布率

pg_statistic 中也存储关于索引表达式值的统计数据

2）扩展统计信息

pg_statistic_ext
pg_statistic_ext_data

5、什么时候更新

手动执行
- 手动 vacuum
- 手动执行 analyze
- 部分DDL语句（如CREATE INDEX）也会更新统计信息
自动更新

autovacuum 达到阈值时触发

autovacuum线程不止负责对过期元组进行清理，同时也负责定期更新表的统计信息。

为什么要把这两个操作放在一起？

PG的MVCC机制数据和数据的旧版本是统一存放在表文件上的，在清理时要进行全表扫描的操作，而统计信息的收集也是需要读取表文件的，这两个操作放在一起做可以在一定程度上节省IO；

清理废旧元组和更新统计信息都是通过收集表的元组变更数据来触发的，共享一套机制，因此放在一起处理也比较方便；

6、什么时候重置/删除

目前还没有找到相关的函数或者命令来重置此类的统计信息

7、其他注意事项

对于大型表，ANALYZE对表内容进行随机抽样，而不是检查每一行。这允许在很短的时间内分析非常大的表。
ANALYZE考虑的样本数量取决于default_statistics_target参数。该参数值越大，直方图的分组越多统计信息就越准确，但同时较大的值会增加ANALYZE执行所需的时间，统计信息收集的开销也会变大，在pg_statistic中也会消耗更多空间。默认值为 100。

也可以通过ALTER TABLE SET STATISTICS命令为每一列单独设置

pg_stats

postgres=# \d pg_stats
                     View "pg_catalog.pg_stats"
         Column         |   Type   | Collation | Nullable | Default 
------------------------+----------+-----------+----------+---------
 schemaname             | name     |           |          |     	--- schema 名
 tablename              | name     |           |          |     	--- 表名
 attname                | name     |           |          |     	--- 列名
 inherited              | boolean  |           |          |     	--- 是否继承列
 null_frac              | real     |           |          |     	--- null空值的比率
 avg_width              | integer  |           |          |     	--- 平均宽度，字节
 n_distinct             | real     |           |          |     	--- 大于零就是非重复值的数量，小于零则是非重复值的个数除以行数
 most_common_vals       | anyarray |           |          |     	--- MCV，高频值
 most_common_freqs      | real[]   |           |          |     	--- MCF，高频值的频率
 histogram_bounds       | anyarray |           |          |     	--- 直方图
 correlation            | real     |           |          |     	--- 物理顺序和逻辑顺序的关联性。
 most_common_elems      | anyarray |           |          |     	--- 高频元素，比如数组
 most_common_elem_freqs | real[]   |           |          |     	--- 高频元素的频率
 elem_count_histogram   | real[]   |           |          |     	--- 直方图（元素）

correlation：物理顺序和逻辑顺序的关联性。（-1 ~ 1）
- 1 - 逻辑顺序与存储的物理顺序相同
- -1 - 逻辑顺序与存储的物理顺序相反
当该值接近-1或+1时，对列的索引扫描估计要比接近0时便宜，这是因为减少了对磁盘的随机访问

二、监控统计信息

1、什么是监控统计信息？

PostgreSQL的统计收集器是一个支持收集和报告服务器活动信息的子系统

对表和索引的访问计数
跟踪每个表中的总行数、每个表的 vacuum 和 analyze 的信息
统计调用用户定义函数的次数以及在每次调用中花费的总时间
也支持报告有关系统正在干什么的动态信息，例如当前正在被其他服务器进程执行的命令以及系统中存在哪些其他连接。这个功能是独立于收集器进程存在的

2、谁收集的统计信息

由stats collector 统计收集进程来进行这类统计信息的收集（15版本取消收集统计信息的进程，统计信息不再使用文件和文件系统，而是使用动态共享内存）

3、如何查看统计信息？

15版本之前，统计收集器通过 UDP 接收统计更新，并通过定期将统计数据写入临时文件来共享统计数据。这些文件被存储在名字由stats_temp_directory参数指定的目录中，默认是pg_stat_tmp；

15版本将统计信息存储在共享内存中。可变编号对象的统计信息存储在 dshash 哈希表中（由动态共享内存支持）。固定编号的统计信息存储在普通共享内存中。

all：包含所有的表/索引

sys：只包含系统表/系统表上的索引

user：只显示用户表/用户表上的索引

1）各个对象级别的统计信息视图

数据库：pg_stat_database
表：pg_stat_all_tables、pg_stat_sys_tables、pg_stat_user_tables
索引：pg_stat_all_indexes、pg_stat_sys_indexes、pg_stat_user_indexes
函数：pg_stat_user_functions

2）各个对象上发生I/O情况的统计视图

表：pg_statio_all_tables、pg_statio_sys_tables、pg_statio_user_tables
索引：pg_statio_all_indexes、pg_statio_sys_indexes、pg_statio_user_indexes
序列：pg_statio_all_sequences、pg_statio_sys_sequences、pg_statio_user_sequences

3）动态统计视图

进程相关：pg_stat_activity
流复制相关：pg_stat_replication、pg_stat_replication_slots
wal相关：pg_stat_wal_receiver
逻辑订阅相关：pg_stat_subscription、pg_stat_subscription_stats
pg_stat_ssl
pg_stat_gssapi
监控进度
- pg_stat_progress_analyze
- pg_stat_progress_create_index
- pg_stat_progress_vacuum
- pg_stat_progress_cluster
- pg_stat_progress_basebackup
- pg_stat_progress_copy

4、什么时候更新

15版本之前，收集器本身最多每PGSTAT_STAT_INTERVAL毫秒（缺省为500ms，除非在编译服务器的时候修改过）发送一次新的报告。但是由track_activities收集的当前查询信息总是最新的。

15版本引入stats_fetch_consistency 参数，有三个值：

none - 每次访问都会从共享内存中重新获取计数器。

最适合监控系统
cache - 第一次访问对象的统计信息会缓存这些统计信息直到事务结束，除非调用pg_stat_clear_snapshot() 。（默认值）

确保重复访问产生相同的值
snapshot - 第一次统计访问缓存当前数据库中可访问的所有统计信息，直到事务结束，除非调用pg_stat_clear_snapshot() 。

在以交互方式检查统计信息时很有用，但开销更高。

5、什么时候删除/重置？

当在服务器启动时执行恢复时（例如立即关闭、服务器崩溃以及时间点恢复之后），所有统计计数器会被重置。

当服务器被干净地关闭时，一份统计数据的永久拷贝被存储在pg_stat子目录中，这样在服务器重启后统计信息能被保持

15版本之前，该过程由 stats collector 进程执行；

15版本，由于取消了 stats collector 进程，该统计信息由 checkpointer检查点进程输出写到文件系统

[postgres@node4 ~]$ cd $PGDATA
[postgres@node4 data5555]$ ll pg_stat
total 0
[postgres@node4 data5555]$ pg_ctl stop 
waiting for server to shut down.... done
server stopped
[postgres@node4 data5555]$ ll pg_stat
total 60
-rw-------. 1 postgres postgres 57573 Dec 13 12:35 pgstat.stat

执行 pg_stat_reset() 函数重置统计信息

当 pg_stat_reset() 清理监控进程的统计信息时，会将pg_stat_all_tables视图的数据全部清为0。analyze后会更新pg_stat_user_tables视图的相关 analyze或者vacuum字段以及n_live_tup、n_dead_tup等字段。

参考

https://aws.amazon.com/cn/blogs/database/understanding-statistics-in-postgresql/

https://xie.infoq.cn/article/a52111ef25e6361f89b0e6d79