开发者日记

外观

执行计划漂移排查实录：ANALYZE 与统计目标调优 ​

问题背景：同一条 SQL，为什么昨天走索引今天 Seq Scan ​

线上最棘手的性能问题，往往不是 SQL 本身写法错误，而是执行计划漂移：同样的语句、同样的索引，数据库昨天选择 Index Scan，今天突然改成 Seq Scan 或错误的 Join 顺序，响应时间从几十毫秒飙升到数秒。

PostgreSQL 优化器是基于成本的优化器（CBO），它并不实时读取全表数据，而是依赖 pg_statistic 中保存的统计信息估算行数。估算行数与真实行数偏差过大时，成本模型就会选错计划。常见诱因包括：大批量写入后未及时 ANALYZE、数据分布严重倾斜、默认统计目标对高基数列不够精细，以及 autovacuum 触发阈值不适配大表。

下面通过一个可复现实验，完整演示计划漂移的发现、定位与修复过程。

方案对比：手动 ANALYZE 还是调大统计目标 ​

处理统计信息问题通常有三类手段，适用场景不同：

  • 手动 ANALYZE table：立即刷新统计信息，适合批量导入、数据修复后的紧急处置，成本低、见效快。
  • 调整 default_statistics_target：全局改变采样桶数量（默认 100），影响所有列，调得过大将拉长 ANALYZE 时间并增加 pg_statistic 占用。
  • 设置列级 STATISTICS：仅对倾斜列、关联列或高频过滤列提高精度，是最精准的做法，可设 0 到 10000。

排查的总体流程是：先用 EXPLAIN (ANALYZE, BUFFERS) 对比估算行数与实际行数，再查 pg_stats 确认统计信息新鲜度与分布，最后决定刷新统计或提高统计目标。

发现计划漂移EXPLAIN 对比行数估算统计陈旧：ANALYZE分布倾斜：调 STATISTICS重新验证执行计划

实操步骤：构造一次真实的统计信息误判 ​

第一步：建立倾斜数据表 ​

sql
-- 构造订单表：status 数据高度倾斜，绝大多数为 'paid'
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     bigint NOT NULL,
    status      text   NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- 写入 100 万行已支付订单
INSERT INTO orders (user_id, status, created_at)
SELECT g, 'paid', now() - (g % 720) * interval '1 hour'
FROM generate_series(1, 1000000) AS g;

-- 仅写入 100 行退款订单
INSERT INTO orders (user_id, status, created_at)
SELECT g, 'refund', now()
FROM generate_series(1, 100) AS g;

CREATE INDEX idx_orders_status ON orders(status);
ANALYZE orders;

此时查询 status = 'refund' 应走索引，因为条件选择率极低。

第二步：观察错误估算与计划变化 ​

先查看优化器对不同取值的行数估算：

sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE status = 'refund';

正常情况下，rows=100 附近的估算会触发 Index Scan。随后模拟业务批量把大量订单改为退款，但不执行 ANALYZE：

sql
-- 批量更新：refund 从 100 行激增到 20 万行
UPDATE orders
SET status = 'refund'
WHERE id <= 199900 AND status = 'paid';

-- 注意：此时不手动 ANALYZE，模拟统计信息滞后
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'refund';

计划中常见的矛盾是 rows=... 估算很小，但 actual rows=200000。优化器误以为索引回表成本低，实际大量回表反而更慢；在另一些参数组合下，它也可能对高频值错误选择 Seq Scan。判断核心不是“索引一定更好”，而是估算行数与实际行数是否同数量级。

第三步：检查统计信息新鲜度 ​

sql
-- 查看最后一次手动/自动统计与分析时间
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- 查看 status 列的常见值、频率与直方图边界
SELECT
    tablename,
    attname,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    null_frac
FROM pg_stats
WHERE tablename = 'orders'
  AND attname = 'status';

如果 last_autoanalyze 停留在更新之前，或 most_common_freqs 中 refund 频率仍接近 0，就可以确认计划漂移来自统计信息滞后。

第四步：刷新统计并验证计划 ​

sql
-- 立即收集全表统计信息
ANALYZE orders;

-- 再次验证估算行数是否接近实际值
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'refund';

ANALYZE 后，优化器能识别 refund 已不是稀有值，从而重新评估 Seq Scan、Index Scan 或 Bitmap Heap Scan 的成本。

第五步：对高基数倾斜列设置列级统计目标 ​

默认 100 个桶对状态码这类低基数字段足够，但对时间范围、地区、租户 ID、标签等列可能过粗。不要一上来全局调参，应先针对问题列提升精度：

sql
-- 将 status 与 created_at 的统计目标提高到 500
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 500;

-- 下一次 ANALYZE 按新目标采样
ANALYZE orders;

-- 验证当前列级统计目标
SELECT attname, attstattarget
FROM pg_attribute
WHERE attrelid = 'orders'::regclass
  AND attname IN ('status', 'created_at');

如果确实需要全局调整，应在观察库整体负载后逐步修改，例如从 100 提到 200，而不是直接设为 1000：

sql
-- 查看当前全局默认统计目标
SHOW default_statistics_target;

-- 会话级验证，不建议未经压测直接写入 postgresql.conf
SET default_statistics_target = 200;
ANALYZE orders;

第六步：让 autovacuum 跟上大表变化 ​

大表即使变化比例不高，绝对变更行数也可能很大。默认 autovacuum 触发阈值约为 50000 + 表行数 * 0.1（受参数影响），千万级表要变更约百万行才触发。可对热点表单独调优：

sql
-- 将自动分析触发比例降到 2%，基础阈值 10000 行
ALTER TABLE orders SET (
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_analyze_threshold = 10000
);

-- 写入、更新特别密集的表，可结合 autovacuum 成本限制
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 10000
);

避坑总结：统计信息调优的三个高频错误 ​

坑一：只看执行节点，不对比 rows 与 actual rows ​

很多同学看到 Seq Scan 就判定异常，盲目加索引或强制 SET enable_seqscan = off。正确做法是执行 EXPLAIN (ANALYZE, BUFFERS)，重点检查估算行数与实际行数。若两者偏差超过数倍，应先怀疑统计信息；若估算准确但计划仍慢，再考虑索引、SQL 改写或成本参数问题。随意关闭优化器开关只是掩盖问题，可能让其他 SQL 更差。

坑二：批量导入后依赖 autovacuum，立即跑核心任务 ​

现象是数据迁移或定时任务刚结束，报表 SQL 全部选择极差计划，过一段时间又自动恢复。原因是 autovacuum/aut oanalyze 是异步触发的，不能保证在大批量 COPY、INSERT ... SELECT、UPDATE 后立刻完成。修正方法是在迁移脚本、数据修复脚本末尾显式执行 ANALYZE 表名；大表可先分析关键 schema，再对核心表执行。

坑三：把 default_statistics_target 当作万能开关 ​

全局调到 1000 甚至 10000 后，ANALYZE 时间明显变长，pg_statistic 膨胀，autovacuum 调度更频繁，业务高峰可能出现额外 I/O 抖动。更稳妥的方式是先用 pg_stats 与错误估算定位具体列，再对倾斜列、范围查询列设置列级 STATISTICS，调整后用真实查询验证，而不是追求“越大越准”。

此外还要注意，ANALYZE 本身收集的是采样统计，不解决跨列相关性问题。例如 status='refund' AND created_at > now() - interval '1 day' 两列存在强关联时，独立条件相乘会低估行数。PostgreSQL 较新版本可评估扩展统计，必要时使用 CREATE STATISTICS ... (dependencies, ndistinct, mcv)，这是列级统计目标之外的另一条修复路径。

统计信息准确，优化器才有稳定决策的基础。排查执行计划漂移时，遵循“复现计划—对比行数—检查统计—精准 ANALYZE 或调优目标—回归验证”的路径，通常能把不可控的偶发慢查询，转化为可度量、可预防的常规性能治理工作。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。