PostgreSQL 统计信息与执行计划调优:ANALYZE 策略、扩展统计与计划回归防护
PG 优化器的质量取决于统计信息的质量。生产里"昨天还快的 SQL 今天走了坏计划"几乎都能归因到三处:统计过期、列间相关性未被建模(默认统计假设独立)、以及成本常数与实际硬件不匹配。PG 18 的 skip scan、自连接消除等新优化又增加了计划变化的面。本文给一套统计管理与计划稳定性工程:ANALYZE 策略、扩展统计用法、成本参数校准与计划回归防护网。
一、读计划的正确姿势:估算 vs 实际
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT ...;
-- 关键对比:每个节点的 "rows=估算" vs "actual rows=实际"
-- 偏差 >10x 的节点 = 统计问题源头;它的下游 join 策略全被带偏
三个必查信号:估算行数与实际行数比(Filter/Join 节点)、Buffers: shared read 与预期不符(缓存假设错)、Sort Method: external merge(work_mem 不足)。PG 18 起还能看 AIO 相关的 io 统计——扫描类节点的 read time 分布更细。
二、ANALYZE 策略:默认 autovacuum 不够用时
autovacuum 顺带 ANALYZE(阈值 20%+50M),对高频小变更表太迟钝。三招分级:
-- 1) 热表单独调低 analyze 阈值
ALTER TABLE articles SET (autovacuum_analyze_scale_factor = 0.01);
-- 2) 批量导入/回填后立即手动 ANALYZE(写进迁移脚本)
ANALYZE articles;
-- 3) 采样精度按倾斜度升级(默认 300 页对十亿表太稀)
ALTER TABLE events SET (autovacuum_analyze_threshold = 10000,
n_distinct = ...); -- 或直接 ANALYZE 后改 statistics target
ALTER TABLE articles ALTER COLUMN status SET STATISTICS 200; -- 列级直方图桶数
判断统计过期的信号:pg_stat_user_tables.last_analyze 落后于写入高峰、慢查询 digest 里同一 queryid 的 rows 估算突变。
三、扩展统计:建模列相关性
默认统计假设列独立,(status='published' AND lang='zh') 的真实选择率可能是两列单独乘积的 3 倍——行数估算崩掉的第一元凶。PG 300+ 的 CREATE STATISTICS 补这个洞:
CREATE STATISTICS st_articles (ndistinct dependencies)
ON status, lang FROM articles;
ANALYZE articles;
-- 表达式也要统计(函数索引的前置条件)
CREATE STATISTICS st_expr (exprs) ON (meta->>'lang'), status FROM articles;
三类对象分别解决:ndistinct(join 行数估算)、dependencies(多列过滤选择率)、mcv(组合值频率,对 IN/OR 列表有效)。哪些列该建:WHERE 里成对出现且业务相关的列——EXPLAIN 偏差大的 join/过滤节点对应的列。
四、成本常数校准:让优化器认识你的硬件
默认 cost 常数是 2005 年的机械盘假设。NVMe + 大缓存的机器上,随机读并不比顺序读贵一个数量级,导致优化器过度回避 index scan:
-- 用 pgbench -i 后实测校准(社区工具 pgfiner/手动二分)
SET seq_page_cost = 1.0; -- NVMe 上从默认 1.0 保持,但随机项要降
SET random_page_cost = 1.1; -- SSD 默认 4.0 → 1.1~1.3(写进 postgresql.conf)
SET effective_io_concurrency = 200; -- NVMe;配合 PG18 AIO 再调 io_workers
校准验证:对已知应走索引的查询,EXPLAIN 是否稳定选 index scan;对大表全扫聚合是否仍正确选 seq scan。random_page_cost 是 SSD 时代误配率最高的参数——4.0 会让优化器系统性偏向嵌套循环+seq 组合。
五、计划回归防护网
计划突变(统计更新/版本升级后)需要秒级发现:
-- 1) 核心查询集固化 + 每日 EXPLAIN diff(CI 化)
CREATE TABLE plan_baseline(queryid bigint, plan_hash text, captured_at timestamptz);
-- plan_hash = md5(EXPLAIN (FORMAT JSON) 输出),变化即告警
-- 2) pg_hint_plan:紧急钉计划(临时止血,不是长期方案)
SELECT /*+ IndexScan(articles idx_author_created) */ ...;
-- 3) PG18 相关注意:enable_self_join_elimination、enable_distinct_reordering
-- 等新优化开关——出现坏计划时先二分这些 GUC 再回退版本
处置顺序固定:确认统计新鲜(ANALYZE)→ 检查扩展统计缺失 → 对照新优化器开关 → pg_hint_plan 临时钉 → 排期修 SQL/索引。直接关优化器特性当永久方案是债。
六、可观测集成
pg_stat_statements 按 queryid 追踪 mean_exec_time 突变(周环比 >50% 告警);pg_stat_user_tables.last_autoanalyze 与写入速率做比值看板;auto_explain 采样生产慢查询的真实计划树落日志——计划调优的数据层就绪后,上面五步才有抓手。
auto_explain.log_min_duration = 500 # ms;采样而非全量
auto_explain.log_analyze = off # on 开销大,仅诊断窗口开
小结
PG 计划质量 = 统计新鲜度(分级 analyze + 导入后手动)× 相关性建模(CREATE STATISTICS 三类对象)× 成本常数诚实反映硬件(random_page_cost 校准)。防护网三件套:plan_baseline 每日 diff、pg_hint_plan 应急钉、新优化开关二分定位。把这套机制跑起来后,"计划突变"从半夜告警变成早上的 diff 报告——调优工作从救火转为巡检,这也是 PG 18 时代优化器越来越聪明之后,DBA 价值最稳定的落点。
评论(0)
登录 后参与评论。