PostgreSQL / 技术文章
PostgreSQL 执行计划突变怎么排查
当线上同一类 SQL 的延迟突然从稳定变成尖刺,最危险的做法是看到 Seq Scan 就立刻加索引,或看到 ANALYZE 很久没跑就直接全库刷新。下面这套处置路径面向后端工程师、DBA 与 SRE:先判断是不是执行计划真的发生回归,再冻结 SQL、参数、统计、索引和会话环境,定位第一个计划分叉点,最后用影响范围最小、可验证且有明确回退方案的方式修复。
先确认“变慢”是不是计划回归
“查询变慢”和“执行计划变差”不是同一件事。磁盘延迟、缓存冷却、锁等待、并发上升、临时文件写入都可能让相同计划变慢;反过来,计划文本发生变化也不一定代表回归,新计划可能只是针对另一组参数更合适。
第一步先把问题缩成同一条 SQL、同一组绑定参数、相近数据规模和相近会话环境。若已启用 pg_stat_statements,可以先找出平均执行时间、共享块读取和临时块写入明显异常的语句,但要记住它保存的是累计统计,不能单独还原某个事故时间窗,因此仍要结合数据库日志、APM 或调用链确定故障发生的具体时段。
SELECT
queryid,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round(total_exec_time::numeric, 2) AS total_ms,
rows,
shared_blks_hit,
shared_blks_read,
temp_blks_written,
query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;如果“好时段”和“坏时段”使用的是不同参数,先不要把差异归因于规划器漂移。对高倾斜列而言,参数本身就可能合理地触发不同扫描或连接路径;真正需要追查的是:相同或等价参数下,计划为什么变了,或者为什么同一计划的执行代价突然变高。
动手修复前先冻结四类证据
事故处理中最常见的失误,是先执行 ANALYZE、改参数或建索引,随后才发现已经无法还原现场。至少要保存四类证据:实际 SQL 与绑定参数、当前执行计划、会话与规划器设置、相关表的统计和索引状态。若能拿到历史上的“好计划”,也要用同样格式保存,后面才能逐节点对齐。
先记录会直接影响规划器或执行内存的关键设置:
SELECT version();
SHOW plan_cache_mode;
SHOW work_mem;
SHOW random_page_cost;
SHOW seq_page_cost;
SHOW effective_cache_size;
SHOW default_statistics_target;再记录相关表的统计刷新状态和修改量。last_analyze 很新并不等于统计仍然代表当前分布;如果短时间发生了批量导入、热点迁移或业务周期切换,统计可能已经落后。
SELECT
schemaname,
relname,
n_live_tup,
n_mod_since_analyze,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relid = 'public.orders'::regclass;
SELECT
indexname,
indexdef
FROM pg_indexes
WHERE schemaname = 'public'
AND tablename = 'orders';最后,在确认语句可以安全执行的环境里抓取实际计划。线上高成本写操作、会锁住大量数据的语句或可能运行很久的 SQL,不应为了诊断直接加 ANALYZE;这类场景先使用普通 EXPLAIN,再在只读副本、预发布或受控会话中复现。
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL, VERBOSE)
SELECT id, customer_id, status, created_at
FROM orders
WHERE customer_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;对齐好计划和坏计划,只找第一个真正分叉点
比较计划时不要从根节点的总耗时开始猜原因,也不要看到最上层 Join 变了就直接调 Join。更有效的方法是从叶子节点向上对齐两份计划,找到第一个同时满足“行为开始不同”和“其输入证据已经发生变化”的节点。
如果坏计划在某个扫描节点第一次出现 estimated rows 与 actual rows 明显失真,而其父节点随后选择了不同 Join 或排序方式,根因通常在这个扫描节点之前:统计过旧、热点值未被 MCV 捕获、直方图分辨率不足、跨列相关性被独立性假设低估,或表达式缺少可用统计。此时父节点的变化更多是误差传播后的结果。
如果 estimated rows 与 actual rows 基本一致,但好计划使用索引、坏计划改成顺序扫描,或 Join 算法在行数接近时发生切换,就把注意力转向代价输入:索引是否被删除或改变、random_page_cost 等设置是否变化、表和索引大小是否显著变化,以及会话是否进入了不同的 prepared statement 计划模式。
如果计划结构几乎没变,行数估算也正常,却只有 actual time、shared read、temp write 或等待时间显著上升,那么这更像运行时退化,而不是坏计划。此时继续“调规划器”只会制造新变量,应转查 I/O、锁、内存溢写、缓存命中率和并发压力。
| 首个异常证据 | 更可能的方向 | 下一步 |
|---|---|---|
| estimated rows 与 actual rows 首次明显偏离 | 统计失真、数据倾斜、相关列 | 检查统计刷新与列分布,再做定向统计修复 |
| 行数估算接近,但扫描或 Join 改变 | 代价输入、索引、会话设置 | 对比索引定义与 planner 相关参数 |
| 同参数下 custom plan 快、generic plan 慢 | 参数敏感与计划缓存 | 隔离验证 custom/generic,不做全局强制 |
| 计划和估算都接近,但实际时间变差 | 运行时资源问题 | 转查 BUFFERS、临时文件、锁与 I/O |
特别检查 prepared statement 的 generic plan
线上“运行一段时间后突然变慢”还有一个容易漏掉的分支:应用使用 prepared statement,前几次执行通常会生成 custom plan,随后系统会比较 generic plan 的估算成本与此前 custom plan 的平均估算成本,并可能开始复用 generic plan。数据分布越倾斜,不同参数之间的最佳计划差异越大,generic plan 越可能只对“平均参数”看起来合理。
诊断时不要直接把 plan_cache_mode 改成全局配置。应在隔离会话中复现同一个 prepared statement,并分别强制 custom 与 generic,比较计划、估算和执行资源。下面的 SQL 只改变当前测试会话:
PREPARE get_orders(bigint, text) AS
SELECT id, customer_id, status, created_at
FROM orders
WHERE customer_id = $1
AND status = $2
ORDER BY created_at DESC
LIMIT 50;
SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
EXECUTE get_orders(42, 'paid');
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
EXECUTE get_orders(42, 'paid');
DEALLOCATE get_orders;如果只有 generic plan 明显退化,根因不是“PostgreSQL 随机选错了计划”,而是参数敏感度与通用计划之间存在结构性冲突。修复可以落在 SQL 形态、索引设计、统计质量或应用侧 prepared statement 策略上;把 force_custom_plan 永久设成全局值通常会把一个局部问题扩散成全库额外的规划开销。
按风险从低到高选择修复
修复顺序应由证据决定,而不是由操作方便程度决定。若首个分叉点来自明显的行数估算错误,优先做定向 ANALYZE,再判断是否需要提高特定列的统计目标;只有当单列统计无法表达跨列关系时,再引入扩展统计。下面示例展示的是逐级加码,而不是要求所有操作同时执行。
ANALYZE VERBOSE orders;
ALTER TABLE orders
ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE orders (customer_id);
CREATE STATISTICS st_orders_customer_status (dependencies, mcv)
ON customer_id, status
FROM orders;
ANALYZE orders;如果行数估算没有问题,却因为索引、表规模或代价参数变化导致路径切换,先恢复缺失的访问路径或修正局部设计,再考虑修改全局代价参数。random_page_cost、seq_page_cost、cpu_tuple_cost 之类的参数会影响大量查询,不应为了救一条 SQL 在事故中临时全局调参。
如果确实需要新索引,先验证它解决的是首个分叉点对应的访问问题,而不是只让某次测试计划“看起来像以前”。生产环境创建大索引还要单独评估锁、I/O 和磁盘空间;对高写入表,通常应考虑 CREATE INDEX CONCURRENTLY 的失败恢复和额外资源开销,而不是把它当成零风险按钮。
验证修复,不要只验证“计划长得一样”
修复完成后,验收对象不是“计划恢复成昨天的文本”,而是同一组关键参数下的估算质量、执行资源和延迟都回到可接受范围。规划器可能用另一条同样优秀的路径达到目标,因此应验证结果,而不是追求计划字符串完全一致。
最少要重新跑与故障参数相同的 EXPLAIN (ANALYZE, BUFFERS, SETTINGS),确认首个分叉点的 estimated rows 与 actual rows 是否重新接近,扫描与 Join 的选择是否与输入规模匹配,shared read、temp write 和总执行时间是否下降。随后再用一组常见参数和一组极端参数回归,避免“修好了热点值,却让普通值变慢”。
事故关闭前,把证据链和回滚条件写入记录:什么时间开始变慢、哪个 queryid 或 SQL 模板受影响、好坏计划的第一个分叉点在哪里、改了哪些统计或索引、验证了哪些参数、什么指标触发回滚。这样下一次再出现计划漂移,团队可以直接复用判断路径,而不是重新从“是不是缺索引”开始猜。
线上处置可以压缩成这张检查单
- 先用日志、APM 或
pg_stat_statements确认真正变慢的 SQL 模板和时间窗,并保存实际绑定参数。 - 在改动前保存坏计划、关键 planner 设置、表统计状态、索引定义和最近相关变更。
- 找到历史好计划或在等价环境复现好状态,从叶子向上定位第一个计划分叉点。
- 先判断 estimated rows 与 actual rows 是否从该点开始失真,再区分统计问题、代价问题、prepared statement 问题和纯运行时问题。
- 只实施能直接解释该分叉点的最小修复;优先定向统计,再考虑扩展统计、索引或 SQL 结构,最后才是影响面大的全局参数。
- 用相同参数、常见参数和极端参数重新验证计划、BUFFERS、临时写入与延迟,并预先写明回滚条件。
- 保存这次事故的证据和决策,使下一次计划回归可以从已知检查点继续,而不是重复试错。