类比MSSQL DBCC SHOW_STATISTICS，PostgreSQL统计与索引维护咨询

查看统计信息当前状态 #

PostgreSQL没有直接对应DBCC SHOW_STATISTICS的命令，但可通过系统视图获取等价的统计细节：

• pg_stats视图：面向用户的统计摘要视图，包含列的高频值、直方图边界、唯一值数量等信息，对应MSSQL统计信息的核心内容：

  SELECT attname, most_common_vals, histogram_bounds, n_distinct
  FROM pg_stats
  WHERE schemaname = 'public' AND tablename = 'your_table' AND attname = 'your_column';
  

• pg_stat_user_tables视图：查看统计信息的更新时间，判断是否过期：

  SELECT relname, last_autoanalyze, last_analyze, n_live_tup, n_dead_tup
  FROM pg_stat_user_tables
  WHERE schemaname = 'public' AND relname = 'your_table';
  

• pg_statistic系统表：获取底层统计细节（如采样率）：

  SELECT starelid::regclass, staattnum::int, stasample, stahistogram
  FROM pg_statistic
  WHERE starelid = 'public.your_table'::regclass;
  

统计信息维护规则 #

PostgreSQL的统计信息维护分为自动、手动两种模式：

• 自动维护：由autovacuum进程触发，默认触发条件是表数据变更行数超过500 + 表行数*20%，可通过autovacuum_analyze_threshold和autovacuum_analyze_scale_factor调整阈值。需确保track_counts参数开启（默认开启），否则autovacuum无法跟踪数据变更。
• 手动维护：执行ANALYZE手动更新统计，针对大表可指定采样率提升效率：

  ANALYZE VERBOSE public.your_table; -- 输出详细更新日志
  ANALYZE public.your_table (your_column); -- 仅更新指定列统计
  ANALYZE public.your_table WITH (sample_rate = 0.1); -- 使用10%采样率
  

索引碎片测量 #

常用pgstattuple扩展（需先创建）获取精准的索引碎片数据：

1. 创建扩展：

   CREATE EXTENSION IF NOT EXISTS pgstattuple;
   

2. 查询指定索引的碎片信息：

   SELECT * FROM pgstatindex('public.your_index');
   

   核心字段：
   • fragmentation：索引碎片率（一般超过30%建议重建）；
   • avg_leaf_density：索引叶子页面平均密度（百分比，越低碎片越严重）；
   • idx_scan：索引被扫描次数（判断索引是否冗余）。

也可通过系统视图粗略估算碎片：

SELECT
  relname AS index_name,
  (idx_tup_read - idx_tup_fetch)::float / idx_tup_read AS approx_fragmentation
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'your_index';

该公式通过读取行数与实际获取行数的差值估算碎片程度，数值越高碎片越严重。

页面密度测量 #

页面密度反映表/索引页面的空间利用率，同样通过pgstattuple查看：

• 查看表的页面密度：

  SELECT relname, avg_page_density FROM pgstattuple('public.your_table');
  

• 查看索引的页面密度：

  SELECT avg_leaf_density FROM pgstatindex('public.your_index');
  

页面密度越低，说明页面空闲空间越多，可能存在膨胀或碎片问题。

统计信息维护合理性判断 #

• 对比数据变更与更新时间：若表中n_dead_tup占比高，但last_autoanalyze/last_analyze时间久远，说明自动维护阈值不合理，需调小autovacuum_analyze_threshold或autovacuum_analyze_scale_factor。
• 检查执行计划：若查询计划出现选择率错误（如错误选择嵌套循环而非哈希连接），大概率是统计信息过期，需手动执行ANALYZE。

索引维护合理性判断 #

• 检查索引使用率：若pg_stat_user_indexes中某索引的idx_scan=0且长期无变化，说明是冗余索引，可考虑删除。
• 碎片与密度阈值：若索引碎片率超30%且页面密度低于60%，建议重建索引，优先使用不锁表的REINDEX CONCURRENTLY：

  REINDEX CONCURRENTLY public.your_index;
  

• 自动清理有效性：若表中n_dead_tup持续偏高，但pg_stat_user_tables的autovacuum_count极少，说明自动清理阈值不合理，需调整autovacuum_vacuum_threshold和autovacuum_vacuum_scale_factor。

内容的提问来源于stack exchange，提问作者aqis