PG的统计信息(一)
一、统计信息
1.1 PG统计信息概述
pg的统计信息主要分为两种：
第一类统计信息是是负载指标“统计信息”（Monitoring stats），通过stat collector进程进行实时采集更新的负载指标，记录一些对磁盘块、表、索引相关的统计信息，SQL语句执行代价信息等。
第二类统计信息是数据分布状态描述“统计信息”（Data distribution stats），这些统计信息为优化器选择最优执行计划提供依据。该类统计信息采集的方式有两种：
后台进程autovacuum lancher触发的统计信息采集
autovacuum : 历史无效数据、冻结事务、xid信息的清理都是由该进程处理。 vacuum : 标准形式的 vacuum 可以和生产数据库操作并行运行（select、insert、update、delete等命令将继续正常工作，但在清理期间你无法使用alter table等命令来更新表的定义）。 vacuum full : vacuum full 类似于表的重建或者说碎片整理，以收回更多磁盘空间但是运行起来更慢，而且vacuum full操作执行期间无法和对此表上的其他操作并发执行。vacuum full不会有后台进程主从触发（只能手动执行）。
手动执行analyze table进行手动采集更新统计信息
1.2 负载指标统计信息
1.2.1 pg_stat_database 指标含义
通过pg_stat_database我们可以大致的了解一个数据库的历史运行情况，比较常见的一个问题定位有：
● 当tup_returned值远大于tup_fetched时，说明该数据库下存在较多全表扫描SQL，结合pg_stat_statments来定位具体慢SQL或者结合pg_stat_user_tables来定位全表扫描相关表
● 当tup_updated的数值比较大时，说明数据库有很频繁的更新，这个时候就需要关注一下vacuum相关的指标和长事务，如果没有及时进行垃圾回收会造成数据膨胀的比较厉害，一定程度会响应表查询效率
● 当temp_files的数值比较大时，说明存在很多的排序，hash，或者聚合这种操作，可以通过增大work_mem减少临时文件的产生，并且同时这些操作的性能也会有较大的提升
postgres=# select * from pg_stat_database where datname='db1'; -[ RECORD 1 ]---------+------------------------------ datid | 16384 //数据库oid datname | db1 //数据库名称 numbackends | 0 //访问当前数据库连接数量 xact_commit | 35170800 //该数据库事务提交总量 xact_rollback | 65 //该数据库事务回滚总量 blks_read | 1047403 //总磁盘物理读的块数，这里read也可能是从page cache读取，如果这里很高需要结合blk_read_time看是否真的存在很多实际从磁盘读取的情况。 blks_hit | 152779457 //在shared_buffer命中的块数 tup_returned | 218640517 //对于表来说是全表扫描的行数，对于索引是通过索引方法返回的索引行数，如果这个值数量明显大于tup_fetched，说明当前数据库存在大量全表扫描的情况。 tup_fetched | 32778249 //指通过索引返回的行数 tup_inserted | 18513456 //插入的行数 tup_updated | 1755886 //更新的行数 tup_deleted | 21499 //删除的行数 conflicts | 0 //与恢复冲突取消的查询次数(只会在备库上发生) temp_files | 20 //产生临时文件的数量，如果这个值很高说明work_mem需要调大 temp_bytes | 334708736 //临时文件的大小 deadlocks | 1 //死锁的数量，如果这个值很大说明业务逻辑有问题 checksum_failures | checksum_last_failure | // blk_read_time | 0 //数据库中花费在读取文件的时间，这个值较高说明内存较小，需要频繁的从磁盘中读入数据文件 blk_write_time | 0 //数据库中花费在写数据文件的时间，pg中脏页一般都写入page cache，如果这个值较高，说明page cache较小，操作系统的page cache需要更积极的写入。 stats_reset | 2020-09-24 16:15:54.395313+08 //统计信息重置的时间
1.2.2 pg_stat_user_tables 指标含义
通过 pg_stat_user_tables，我们可以知道当前数据库下哪些表发生全表扫描频繁，哪些表变更比较频繁，对于变更较频繁的表可多关注其vacuum相关的指标，避免表膨胀。
db1=# select * from pg_stat_user_tables where relname='t1'; -[ RECORD 1 ]-------+------------------------------ relid | 17087 //表的oid schemaname | public //schema模式 relname | t1 //表名称 seq_scan | 66 //发生全表扫描次数 seq_tup_read | 602468 //全表扫描数据行数，如果这个值很大说明对这个表进行sql很有可能都是全表扫描，需要结合具体的执行计划来看 idx_scan | 149 //索引扫描测试 idx_tup_fetch | 140 //通过索引扫描返回的行数 n_tup_ins | 10000 //插入数据行数 n_tup_upd | 40 //更新数据行数 n_tup_del | 1 //删除数据行数 n_tup_hot_upd | 35 //hot update的数据行数，这个值与n_tup_upd越接近说明update的性能较好，更新数据时不会更新索引。 n_live_tup | 9999 //活着的行数量 n_dead_tup | 34 //死亡的行数量,无效数据行 n_mod_since_analyze | 22 //上次analyze的时间 last_vacuum | //上次手动vacuum的时间 last_autovacuum | //上次autovacuum的时间 last_analyze | //上次手动analyze的时间 last_autoanalyze | 2020-10-12 14:21:44.597134+08 //上次自动analyze的时间 vacuum_count | 0 //vacuum的次数 autovacuum_count | 0 //autovacuum的次数 analyze_count | 0 //analyze的次数 autoanalyze_count | 1 //自动analyze的次数
1.2.3 pg_stat_user_indexes 指标含义
通过pg_stat_user_indexes我们可以查看对应索引的使用情况，可以协助我们判断哪些索引当前基本不使用，对这些无效的冗余索引，可进行索引删除。
db1=# select * from pg_stat_user_indexes where relname='t1'; -[ RECORD 1 ]-+-------- relid | 17087 //相关表的oid indexrelid | 17094 //索引oid schemaname | public //schema模式 relname | t1 //表名 indexrelname | t1_pkey //索引名 idx_scan | 149 //通过索引扫描的次数，如果这个值很小，说明这个索引很少被用到，可以考虑进行删除 idx_tup_read | 154 //通过任意索引方法返回的索引行数 idx_tup_fetch | 140 //通过索引方法返回的数据行数 -[ RECORD 2 ]-+-------- relid | 17087 indexrelid | 17153 schemaname | public relname | t1 indexrelname | idx_id2 idx_scan | 0 idx_tup_read | 0 idx_tup_fetch | 0
