一条 SET 命令,让同一个查询从平均 28ms 变成平均 264ms。表没换、数据没换、硬件没换、SQL 也没换,唯一的变化是告诉 PostgreSQL:别用索引,改用顺序扫描。

打开网易新闻 查看精彩图片

这条命令来自 PostgreSQL 19 的新模块 pg_plan_advice。它是一个 contrib 模块,允许你把一段提示字符串交给查询规划器,然后看它照做,或者看它告诉你为什么没照做。测试时 PostgreSQL 19 还停留在 RC2,11 月 14 日从 Docker Hub 拉取的镜像是 postgres:19rc2,尚未正式发布,一切细节都还可能变动。

测试的初衷是找一个“提示能救场”的案例。结果没找到。找到的是一套精确、文档齐全的、把健康查询变慢的方法,以及一个对自己能力边界相当诚实的反馈系统。

测试环境怎么搭的

两张表:5 万名客户、200 万条订单。status 字段做了倾斜分布,0.5% 的订单是 refunded,其余是 shipped。在 status 上建索引后,查 refunded 很便宜。这种表结构几乎每个生产库都有一张。

规划器自己选的是:orders 走索引扫描,再和小表 customers 的顺序扫描做哈希连接。三次重复运行分别是 24.8ms(冷启动)、16.5ms、18.0ms,这就是后续对比的基线。

pg_plan_advice 分两半用。EXPLAIN (PLAN_ADVICE) 会输出一段能复现当前计划的建议字符串;SET pg_plan_advice.advice = '...' 则把字符串喂回去,规划器尝试照办,并反馈执行得如何。

强行指定一个错误计划

建议词汇表里有 SEQ_SCAN、INDEX_SCAN、HASH_JOIN、NESTED_LOOP_PLAIN,还有十几个用于合并连接、分区计划和并行 worker 的选项。测试用它强制了规划器已经否决的那个方案:orders 走顺序扫描,而不是索引扫描。

只强制扫描方式,和同时强制扫描方式加连接方式,代价差不多。也就是说,扫描选择几乎承担了全部代价;一旦输入从索引里的 1 万行变成整张表,连接策略的影响就很小了。

并发下差距进一步拉大。两个计划各跑 10 个客户端、持续 10 秒的 pgbench:单连接时是 2.6 倍惩罚,十个连接时变成 9.3 倍。十个顺序扫描同时扫 200 万行的表,争抢同样的缓冲页和 CPU,比十个索引探测激烈得多。单连接的数字,低估了错误提示在生产中的代价。

始终没找到“提示帮上忙”的场景

这部分最值得说。原本预期大部分时间会花在寻找“规划器判断失误、提示来纠正”的查询上。于是构造了一个 region 和 tier 完全相关的表结构(两者都由同一个 id % 3 推导),按规划器对多数谓词仍在使用的独立性假设,它应该会低估两列联合过滤的结果行数。

它确实低估了:一个已知匹配 16666 行的过滤条件,被估算成大约只有三分之一。但它最终选的哈希连接仍然是最快的方案,因为表足够小,估算偏差没有改变哪个计划最便宜。无论怎么加大相关性倾斜,都没能让 PostgreSQL 19 RC2 在这个结构上选出一个真正糟糕的默认计划。提供的每一段建议字符串,要么和规划器原本的意图一致,要么让事情变糟。

这和事先读到的说法吻合:这个工具是用来固定一个你本来就信任的计划,比如在连接池下、或某个参数偶尔干扰规划器时使用,而不是用来实时纠正糟糕的行数估算。只是不亲手试上一个小时、试到失败为止,很难真的相信这一点。

它会拒绝什么

三种情况,以及 PostgreSQL 给出的原始反馈:

  • 索引不存在:INDEX_SCAN(o no_such_index),反馈为 matched、inapplicable、failed。
  • 同一张表上互相矛盾的建议:SEQ_SCAN(o) 与 INDEX_SCAN(o idx_orders_status) 都被标记为 matched、conflicting、failed。
  • 表别名拼错或缺失:SEQ_SCAN(zzz),反馈只有 not matched。

前两种被安全拒绝,查询按规划器自己选的计划执行,不会跑出一个坏计划。第三种才是真正让人担心的:没有报错,没有警告,查询就像你根本没提供建议一样执行。测试时这正合心意,但如果你在连接池的启动脚本里拼错了一个别名,就会在毫不知情的情况下拿不到你以为已经配置好的计划。

有一个文档里写明的补救办法:pg_plan_advice.feedback_warnings。打开后,同样的拼写错误会产生一条真实的日志:WARNING: supplied plan advice was not enforced,DETAIL 里写明建议的反馈是 not matched。打开后行为与文档一致。问题在于它默认关闭,也就是说,最容易坑到人的失败模式——建议静默失效——恰恰需要你主动开启才会被告知。

文档提到的坑,和没提到的坑

文档警告:已缓存的预备语句计划不会察觉 pg_plan_advice.advice 的新值,除非执行 DISCARD PLANS。这一点被完整复现了。先设 plan_cache_mode = force_generic_plan 让第一次 EXECUTE 缓存计划,把建议设为顺序扫描,执行一次(如要求拿到了顺序扫描),再把建议改成索引扫描并再次执行——结果仍是并行顺序扫描。只有执行 DISCARD PLANS 之后,下一次执行才切换到索引扫描。

文档没提的是:对那次过期的缓存执行,EXPLAIN (PLAN_ADVICE) 完全不显示 Supplied Plan Advice 段落,因为没有发生重新规划。如果你靠这个段落判断新建议是否生效,它的缺席看起来和“什么都没提供”一模一样,而不是“你的建议被忽略了”。按这个逻辑,人多半会先怀疑自己拼错了,而不是怀疑缓存。

闲置时的开销

每个事务都用 LOAD 重新加载模块时,一个单行 count 的平均延迟是 0.457ms,完全不加载模块时是 0.408ms,多出 12%,这是每次调用 LOAD 的成本,不是模块规划钩子的成本。通过 shared_preload_libraries 预加载一次后,同样的查询是 0.377ms,与不装模块没有区别。要在生产里用,就预加载,别按连接去 LOAD。

测试中判断错的地方

将近一半的测试时间花在一个后来被证伪的猜想上:带参数的预备语句在倾斜的 status 列上,会在 Postgres 惯常的五次执行阈值之后翻车成糟糕的通用计划,理由是通用计划会对未绑定参数使用朴素的均匀选择性估算。事实并非如此。PostgreSQL 的规划器在构建通用计划时已经使用了该列的最常见值统计,所以第六次执行和第一次执行一样选了索引扫描,耗时从 9.99ms 降到 3.3ms,根本没有翻转。那篇“提示修复计划退化”的文章写不成了。诚实的结论是:规划器不需要修,而前面那个相关性列结构,是同一目标的第二次尝试,同样没能造出一个糟糕的默认计划。

自己跑一遍

需要 Docker,大约十分钟。用 postgres:19rc2 起容器,建两张表、灌入 5 万客户和 200 万订单、在 status 上建索引、执行 ANALYZE,然后 LOAD 'pg_plan_advice',先用 EXPLAIN 看规划器自己的选择,再用 SET pg_plan_advice.advice = 'SEQ_SCAN(o)' 强制顺序扫描并对比。

要复现并发数字,把最后的 EXPLAIN 换成带同一行 SET 的 pgbench 自定义脚本,用 pgbench -c 10 -T 10 分别跑有建议和无建议两个版本。

在任何永久位置固定计划之前——比如连接池的启动脚本或 postgresql.conf——先用至少十个客户端跑一遍自己的强制扫描测试。单连接的数字是 2.6 倍,十客户端是 9.3 倍。生产流量更像第二个数字,而不是第一个,而测试差点在发现这一点之前就收工了。