PG的统计信息(三)
1.3 数据分布类统计信息
1.3.1 pg_stats
通过对pg_stats的查询，可以查看每个字段的数据分析统计信息，类似SQL Server的直方图，为优化器选择最佳执行计划提供依据，pg_stats只有管理员账号才可以访问。
db1=# select * from pg_stats where tablename='t1' limit 1;
-[ RECORD 1 ]----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public //schema模式
tablename | t1 //表名
attname | id //列名
inherited | f //如果为真，那么说明还字段存在包是继承而来的子字段，不只是指定表的值。
null_frac | 0 //该字段为空记录数的百分比
avg_width | 4 //该字段每行的平均长度
n_distinct | -1 //如果大于零，就是在字段中唯一数值的估计数目。如果小于零， 就是唯一数值的数目被行数除的负数。用负数形式是因为ANALYZE 认为独立数值的数目是随着表增长而增长； 正数的形式用于在字段看上去好像有固定的可能值数目的情况下。比如， -1 表示一个唯一字段，独立数值的个数和行数相同。
most_common_vals | //该字段最常用数值的列表。如果看上去没有啥数值比其它更常见，则为 null。主键一般为null。
most_common_freqs | //该字段最常用数值的频率，也就是说，每个出现的次数除以行数。 如果most_common_vals是 null ，则为 null。
histogram_bounds | {1,100,200,300,400,500,600,700,800,900,1000,1100,1200,1300,1400,1500,1600,1700,1800,1900,2000,2100,2200,2300,2400,2500,2600,2700,2800,2900,3000,3100,3200,3300,3400,3500,3600,3700,3800,3900,4000,4100,4200,4300,4400,4500,4600,4700,4800,4900,5000,5100,5200,5300,5400,5500,5600,5700,5800,5900,6000,6100,6200,6300,6400,6500,6600,6700,6800,6900,7000,7100,7200,7300,7400,7500,7600,7700,7800,7900,8000,8100,8200,8300,8400,8500,8600,8700,8800,8900,9000,9100,9200,9300,9400,9500,9600,9700,9800,9900,10000} //一个数值的列表，它把字段的数值分成几组大致相同热门的组。
correlation | 1 //统计与字段值的物理行序和逻辑行序有关。它的范围从 -1 到 +1 。 在数值接近 -1 或者 +1 的时候，在字段上的索引扫描将被认为比它接近零的时候开销更少， 因为减少了对磁盘的随机访问。如果字段数据类型没有<操作符，那么这个字段为null
most_common_elems | //经常在字段值中出现的非空元素值的列表。（标量类型为空。）
most_common_elem_freqs | //最常见元素值的频率列表，也就是，至少包含一个给定值的实例的行的分数。 每个元素频率跟着两到三个附加的值；它们是在每个元素频率之前的最小和最大值， 还有可选择的null元素的频率。（当most_common_elems 为null时，为null）
elem_count_histogram | //该字段中值的不同非空元素值的统计直方图，跟着不同非空元素的平均值。（标量类型为空。）1.3.2 pg_statistic
pg_statistic 是基于pg_stats的视图，以更加友好以及可读的方式展现统计信息，普通用户可以访问。
db1=# select * from pg_statistic limit 1;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
starelid | 16698
staattnum | 1
stainherit | t
stanullfrac | 0
stawidth | 4
stadistinct | -1
stakind1 | 2
stakind2 | 3
stakind3 | 0
stakind4 | 0
stakind5 | 0
staop1 | 97
staop2 | 97
staop3 | 0
staop4 | 0
staop5 | 0
stacoll1 | 0
stacoll2 | 0
stacoll3 | 0
stacoll4 | 0
stacoll5 | 0
stanumbers1 |
stanumbers2 | {1}
stanumbers3 |
stanumbers4 |
stanumbers5 |
stavalues1 | {1,100,200,300,400,500,600,700,800,900,1000,1100,1200,1300,1400,1500,1600,1700,1800,1900,2000,2100,2200,2300,2400,2500,2600,2700,2800,2900,3000,3100,3200,3300,3400,3500,3600,3700,3800,3900,4000,4100,4200,4300,4400,4500,4600,4700,4800,4900,5000,5100,5200,5300,5400,5500,5600,5700,5800,5900,6000,6100,6200,6300,6400,6500,6600,6700,6800,6900,7000,7100,7200,7300,7400,7500,7600,7700,7800,7900,8000,8100,8200,8300,8400,8500,8600,8700,8800,8900,9000,9100,9200,9300,9400,9500,9600,9700,9800,9900,10000}
stavalues2 |
stavalues3 |
stavalues4 |
stavalues5 |统计信息相关参数：
track_counts : 控制是否采集表和索引上访问的统计信息，默认为ON
track_functions ：控制是否采集函数调用次数和执行时间的统计信息，默认为"none"。"none"表示不采集,"pl"表示仅采集过程语言函数统计信息,"all"表示采集所有函数统计信息，包括SQL和C语言
track_activities : 控制是否采集会话执行SQL的相关统计信息，默认为ON，这部分信息可在pg_stat_activity中查看
track_activity_query_size : 控制在pg_stat_activity视图的query字段的长度限制，默认为1KB，超过该参数大小SQL会被截断
track_io_timing : 控制是否采集IO调用相关统计信息，默认为OFF。若该参数打开explain使用buffers选项时可显示具体IO调用时间，且采集的IO相关信息可以在pg_stat_database、pg_stat_statements中查看
update_process_title : 控制当后台进程正在执行命令时，是否更新其title信息，在Linux下默认为ON
stats_temp_directory : 设置存储临时统计数据的路径，默认为pg_stat_tmp
1.4 统计信息更新
1.4.1 统计信息自动更新
1、统计信息自动更新机制
autovacuum进程周期性触发自动更新。autovacuum参数打开时（默认开启），autovacuum lancher进程根据autovacuum_naptime参数设置作为统计信息进程被唤醒的频率（默认1min），以autovacuum_max_workers参数设置作为每次更新统计信息采集时启动的工作线程数（默认为3）
表字段增删改记录行数达到一定的界限时，autovacuum进程触发更新。判定规则为 ： 当表上增删改的行数 >= autovacuum_vacuum_scale_factor * reltuples(表上记录数) + autovacuum_vacuum_threshold。
每次统计信息的采集仅仅会按照一定的比例抽样采集统计数据，由参数default_statistics_target作为样本容量，这种方式很容易造成当表数据量较大时采集数据无法准确预估出当前字段的准确的数据分布信息，需要根据实际情况进行调整
对于表字段，可以通过对 statistics 设置，指定该字段统计信息采集数据行占表行数的比例，针对大数据量表增强其统计信息采集的准确度。statistics信息在pg_attribute表中查看，可通过alter进行变更设置。
2、相关参数与变更
-- autovacuum进程自动触发统计信息的更新 db1=# show autovacuum; -[ RECORD 1 ]-- autovacuum | on db1=# show autovacuum_naptime; -[ RECORD 1 ]------+----- autovacuum_naptime | 1min db1=# show autovacuum_max_workers; -[ RECORD 1 ]----------+-- autovacuum_max_workers | 3 -- autovacuum进程根据表变更行数触发统计信息的更新 db1=# show autovacuum_vacuum_threshold; -[ RECORD 1 ]---------------+--- autovacuum_vacuum_threshold | 50 db1=# show autovacuum_vacuum_scale_factor; -[ RECORD 1 ]------------------+---- autovacuum_vacuum_scale_factor | 0.2 -- 按照表容量进行抽样采集控制参数 db1=# show default_statistics_target; -[ RECORD 1 ]-------------+---- default_statistics_target | 100 -- 按照表数据量一定比例对表字段进行抽样采集控制参数调整 db1=# select * from pg_attribute where attrelid=(select oid from pg_class where relname='t1') and attname='id2'; -[ RECORD 1 ]-+------ attrelid | 17087 attname | id2 atttypid | 23 attstattarget | -1 attlen | 4 attnum | 2 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | i attnotnull | f atthasdef | f atthasmissing | f attidentity | attgenerated | attisdropped | f attislocal | t attinhcount | 0 attcollation | 0 attacl | attoptions | attfdwoptions | attmissingval | db1=# alter table t1 alter column id2 set statistics 200; ALTER TABLE db1=# select * from pg_attribute where attrelid=(select oid from pg_class where relname='t1') and attname='id2'; -[ RECORD 1 ]-+------ attrelid | 17087 attname | id2 atttypid | 23 attstattarget | 200 attlen | 4 attnum | 2 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | i attnotnull | f atthasdef | f atthasmissing | f attidentity | attgenerated | attisdropped | f attislocal | t attinhcount | 0 attcollation | 0 attacl | attoptions | attfdwoptions | attmissingval |
3、pg_stat_all_tables
该表记录了所有表的最后一次更新历史信息，一定程度上可以根据该表的统计信息来评估autovacuum_vacuum_scale_factor设置是否合理。
db1=# delete from t1 where id>8000; DELETE 2000 db1=# select * from pg_stat_all_tables where relid=(select oid from pg_class where relname='t1'); -[ RECORD 1 ]-------+------------------------------ relid | 17087 schemaname | public relname | t1 seq_scan | 71 seq_tup_read | 622500 idx_scan | 150 idx_tup_fetch | 2140 n_tup_ins | 10000 n_tup_upd | 40 n_tup_del | 2001 n_tup_hot_upd | 35 n_live_tup | 7999 n_dead_tup | 2034 n_mod_since_analyze | 2000 //上次更新变更2000行记录 last_vacuum | last_autovacuum | last_analyze | 2020-10-25 23:26:54.623004+08 last_autoanalyze | 2020-10-12 14:21:44.597134+08 vacuum_count | 0 autovacuum_count | 0 analyze_count | 1 autoanalyze_count | 1
1.4.2 手动收集统计信息
1、语法：
analyze verbose tablename (col1,col2,...); //verbose表示打印处理进度，可直接重新采集某一张表统计信息，也可以采集某张表的某个字段的统计信息
2、示例：
db1=# analyze verbose t1; INFO: analyzing "public.t1" INFO: "t1": scanned 52 of 52 pages, containing 7999 live rows and 2034 dead rows; 7999 rows in sample, 7999 estimated total rows ANALYZE db1=# select * from pg_stat_all_tables where relid=(select oid from pg_class where relname='t1'); -[ RECORD 1 ]-------+------------------------------ relid | 17087 schemaname | public relname | t1 seq_scan | 71 seq_tup_read | 622500 idx_scan | 150 idx_tup_fetch | 2140 n_tup_ins | 10000 n_tup_upd | 40 n_tup_del | 2001 n_tup_hot_upd | 35 n_live_tup | 7999 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | 2020-10-25 23:28:24.553443+08 last_analyze | 2020-10-25 23:27:46.143074+08 //上一次手动更新时间 last_autoanalyze | 2020-10-12 14:21:44.597134+08 vacuum_count | 0 autovacuum_count | 1 analyze_count | 2 autoanalyze_count | 1
