Azure PostgreSQL分区表count(*)行数预估错误及索引选择问题

1. 修复执行计划行数预估错误 #

针对单个分区预估行数偏差的问题，可按以下步骤排查修复：

• 调整列级统计目标：对于相关性极高的列（如batch_id和efftv_dt，相关性0.9995），默认统计目标可能不足以捕捉数据分布，手动设置更高的统计值后重新分析：

  ALTER TABLE stat_201408 ALTER COLUMN efftv_dt SET STATISTICS 1000;
  ALTER TABLE stat_201408 ALTER COLUMN batch_id SET STATISTICS 1000;
  VACUUM ANALYZE stat_201408;
  

• 验证统计信息准确性：查询pg_stats视图确认列的n_distinct、correlation等统计值是否与实际数据匹配：

  SELECT attname, n_distinct, correlation FROM pg_stats WHERE tablename = 'stat_201408';
  

• 重置统计后重新收集：若统计信息存在异常，可临时重置统计（生产环境需谨慎）后重新分析：

  SELECT pg_stat_reset();
  VACUUM ANALYZE stat_201408;
  

• 分区表统计优化：PostgreSQL 13默认合并分区统计，若分区数据分布不均，开启enable_partitionwise_aggregate可让优化器单独考虑每个分区的统计：

  SET enable_partitionwise_aggregate = on;
  

2. 强制PostgreSQL使用指定索引 #

PostgreSQL支持类似Oracle的HINT语法，通过C风格注释加+号指定索引扫描方式：

• Index Only Scan HINT示例：

  SELECT count(*) FROM stat_201408 /*+ IndexOnlyScan(stat_201408 stat_efftv_dt_acct_id_idx) */;
  

• 普通Index Scan HINT示例：

  SELECT count(*) FROM stat_201408 /*+ IndexScan(stat_201408 stat_efftv_dt_acct_id_idx) */;
  

注意：HINT是优化器的建议，若索引不可用（如损坏、未vacuum清理）或成本差异过大，优化器仍可能忽略。不推荐使用SET enable_seqscan = off这类全局临时设置，优先使用HINT。

3. 索引选择逻辑与最优索引设计 #

列不同值数量对索引选择的影响 #

列的不同值数量（n_distinct）是优化器计算索引选择性的核心因素之一，但不是唯一决定项。优化器还会综合索引大小、扫描成本、是否支持Index Only Scan等维度判断。

为何优先选择stat_idx1 #

stat_idx1是acct_id单列索引，而stat_efftv_dt_acct_id_idx是复合索引：

• 复合索引的单条索引项体积更大，扫描时需要读取更多数据块，成本更高；
• acct_id的n_distinct（1271151）远高于efftv_dt（21），优化器可能认为其选择性更好，但对于无过滤条件的count(*)，选择性其实不影响最终结果，核心是索引大小。

提升count(*)性能的最优索引设计 #

对于无过滤条件的count(*)，最优索引是体积最小的非空单列覆盖索引，因为Index Only Scan只需扫描索引，无需回表：

• 选择最窄的非空列创建单列索引（如efftv_dt，仅占8字节，索引体积远小于复合索引）；
• 验证索引大小：

  SELECT pg_size_pretty(pg_relation_size('stat_idx1')) AS stat_idx1_size,
         pg_size_pretty(pg_relation_size('stat_efftv_dt_acct_id_idx')) AS composite_idx_size;
  

• 确保索引的relallvisible与表一致（当前分区relallvisible等于relpages，已满足），避免Index Only Scan时检查堆数据。

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