在 PostgreSQL 中减少行数估计误差
I’m happy to translate the article for you, but I’ll need the full text you’d like translated. Could you please paste the content (or the portion you want translated) here? I’ll keep the source line and all formatting exactly as you requested.
介绍
PostgreSQL 的查询规划器依赖表统计信息来估计每个操作将处理的行数（估计行数），随后基于这些估计选择最优的执行计划。当估计行数与实际行数相差甚远时，规划器可能会选出次优计划，导致查询性能出现严重下降。
本文将逐步演示我用来降低行数估计误差的 四种方法，按侵入性从低到高排序。
由于保密限制，我无法提供真实的 SQL 或执行计划；本文侧重于诊断思路和所采用的技术手段。
这些方法适用于任何现代 PostgreSQL 版本，因为底层机制（autovacuum、pg_statistic、扩展统计）在各版本之间保持稳定。
1. 让统计信息保持最新
目标表的 更新频率很高。最初的假设是统计信息已经过时。
- 在 PostgreSQL 中，autovacuum 守护进程会自动运行 ANALYZE，以更新存放在
pg_statistic中的统计信息。 - 对于写入密集的表，自动 ANALYZE 可能跟不上，导致统计信息与实际情况产生偏差。
为单表调整 autovacuum‑ANALYZE 频率
与其在 postgresql.conf 中修改全局设置，我选择针对该表调节两个关键参数：
| 参数 | 含义 | 默认值 |
|---|---|---|
autovacuum_analyze_threshold | 在触发自动 ANALYZE 前，最少需要的元组修改数 | 50 |
autovacuum_analyze_scale_factor | 在阈值基础上再加的表大小比例 | 0.1（10 %） |
ALTER TABLE table_name SET (
autovacuum_analyze_threshold = 0,
autovacuum_analyze_scale_factor = 0.01
);将 autovacuum_analyze_threshold 设为 0 并把比例因子降至 0.01，意味着只要表的 1 % 被修改就会触发自动 ANALYZE。
验证方法：
SELECT relname,
last_autoanalyze,
n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'table_name';last_autoanalyze– 最近一次自动 ANALYZE 的时间戳。n_mod_since_analyze– 自上次 ANALYZE 以来的行修改数。
有关每个表的存储参数的完整列表，请参阅 PostgreSQL 文档中的 storage parameters。
2. 增大抽样规模
在确保统计信息是最新的前提下，下一步的假设是 ANALYZE 已经足够频繁，但抽样规模太小，导致统计不够准确。
PostgreSQL 的 ANALYZE 会从每列抽取样本并存储：
- 最常出现的值（MCV）
- 直方图
这些信息的精度由 default_statistics_target（默认 = 100）决定，它控制直方图桶的数量以及 MCV 条目的数量。
为单列提升统计目标
ALTER TABLE table_name
ALTER COLUMN column_name SET STATISTICS 500;- 建议： 对经常出现在
WHERE子句中的列，将目标设为 500–1000。 - 权衡： 较高的值会延长 ANALYZE 的执行时间，并使
pg_statistic体积增大。
注意： 使用
SET STATISTICS更改统计目标后，必须执行ANALYZE（或等待下一次自动 ANALYZE）才能使新设置生效。
3. 使用扩展统计捕获列之间的相关性
即使拥有最新且精确的基础统计信息，行数估计误差仍可能存在，因为规划器的 估计模型本身有结构性限制。
默认情况下，PostgreSQL 假设不同列上的条件是 相互独立 的。当这种假设被违反时，规划器会独立地相乘选择性，往往导致对实际行数的 严重低估。
row count.
何时会出现这种情况？
- 表中有两列
a1和a2。 - 它们之间存在 函数依赖（例如，
a1决定a2）。 WHERE子句同时对这两列设置了条件。
一个具体的例子：country（国家）和 city（城市）。已知国家在很大程度上决定了可能的城市集合。优化器把每个条件的选择性视为相互独立，从而得到的估计值远低于实际值。
创建扩展统计信息
CREATE STATISTICS stat_name ON a1, a2 FROM table_name;CREATE STATISTICS 支持三种统计信息：
ndistinct– 多列唯一计数dependencies– 函数依赖mcv– 多列最常见值
如果省略 KIND 子句，三者都会被收集，这正是我作为起点所做的。
注意：
CREATE STATISTICS只定义统计对象。实际的统计信息要等到对表执行 ANALYZE 时才会填充。
更多细节请参阅 PostgreSQL 文档中的 CREATE STATISTICS。
4. 使用规划器提示（pg_hint_plan）—— 最后手段
当基于统计的信息仍不足以解决问题时，pg_hint_plan 扩展提供了一种通过 SQL 注释提示 直接控制规划器行为 的方式。
覆盖行数估计
/*+ Rows(table_name #1000) */ SELECT ...#– 将估计值设为绝对数值（此处为 1 000）。- 也可以使用
+、-或*来 增加、减少 或 乘以 规划器原始的估计值。
基于提示的方法的缺点
| 问题 | 说明 |
|---|---|
| 对数据变化脆弱 | 固定的行数在数据量变化时会变得不准确。 |
| 可维护性降低 | 不熟悉提示的团队成员可能会感到困惑。 |
| 掩盖根本原因 | 提示可能隐藏应通过改进统计或模式来解决的底层问题。 |
建议： 仅在 统计方法已全部尝试完毕，或作为 临时措施（在实现更永久的解决方案期间）时才使用提示。
调查根本原因
本文介绍了四种减少 PostgreSQL 行数估计误差的方法，按侵入性递增排序：
- 调优 autovacuum 频率 – 统计信息是否陈旧？
- 提高统计目标 – 采样规模是否足够？
- 创建扩展统计信息 – 规划器能否考虑跨列相关性？
- 使用提示子句 – 当仅靠统计信息无法解决问题时的最后手段。
系统化故障排查工作流
面对行数估计错误时，系统化的方法效果最佳：
- 运行
EXPLAIN ANALYZE– 将规划器估计的行数与查询实际返回的行数进行比较。 - 检查统计信息的新鲜度 – 若统计信息过时，调整 autovacuum 设置或手动执行
ANALYZE。 - 提升精度 – 提高
default_statistics_target（或针对单列的目标），为规划器提供更大的样本。 - 解决结构性限制 – 使用
CREATE STATISTICS捕获规划器默认忽略的多列相关性。 - 使用提示子句 – 作为最后的手段，使用诸如
pg_hint_plan等扩展强制特定的连接顺序或扫描方式。
希望本文能在您的故障排查过程中提供有价值的参考。
参考文献
- PostgreSQL 文档：表存储参数
- PostgreSQL 文档：CREATE STATISTICS
pg_hint_plan（GitHub）：https://github.com/ossc-db/pg_hint_plan