网易首页 > 网易号 > 正文 申请入驻

一条SET命令,把28ms的查询拖到264ms

0
分享至

一条 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 倍。生产流量更像第二个数字,而不是第一个,而测试差点在发现这一点之前就收工了。

特别声明:以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布,本平台仅提供信息存储服务。

Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.

相关推荐
热点推荐
除了性生活就是打麻将,揭露中国2000多个县城,普通人的生活实录

除了性生活就是打麻将,揭露中国2000多个县城,普通人的生活实录

墨策讲历史
2026-11-12 12:47:12
丈夫变心20年,妻子没提过一句离婚,孙子满月宴上,丈夫却崩溃了

丈夫变心20年,妻子没提过一句离婚,孙子满月宴上,丈夫却崩溃了

兰姐说故事
2025-04-30 20:00:14
乐道L90上线订阅,2999元开一个月

乐道L90上线订阅,2999元开一个月

E车汇
2026-11-17 16:10:47
热搜第一!“我愿意为自己的行为负责,只求放过我的家人!”14岁初中生发长文喊话明星虞书欣引热议

热搜第一!“我愿意为自己的行为负责,只求放过我的家人!”14岁初中生发长文喊话明星虞书欣引热议

笨鸟摘文
2026-11-18 11:55:31
腾讯发文:已到生死存亡时刻,未来不一定要那么多管理岗位,需要一半人去打新的战役

腾讯发文:已到生死存亡时刻,未来不一定要那么多管理岗位,需要一半人去打新的战役

21世纪经济报道
2026-11-17 07:38:59
沐神入局!李沐杀入世界模型,目标打造商业AI沙盘

沐神入局!李沐杀入世界模型,目标打造商业AI沙盘

魏家东
2026-11-17 09:06:47
还记得你和异性做过最疯狂的事吗?网友:我差不多就是这样和同事发生了

还记得你和异性做过最疯狂的事吗?网友:我差不多就是这样和同事发生了

带你感受人间冷暖
2026-11-14 15:59:30
81.4%扑救率全欧第一,这个开拖拉机的成了英格兰一门

81.4%扑救率全欧第一,这个开拖拉机的成了英格兰一门

日常碎碎念啊
2026-11-17 12:29:46
江苏三大国企是哪三个?

江苏三大国企是哪三个?

网观天下
2026-11-18 07:33:22
2020年杭州杀妻碎尸案,丈夫将妻子绞碎后,耗两吨水冲进下水道

2020年杭州杀妻碎尸案,丈夫将妻子绞碎后,耗两吨水冲进下水道

历史品鉴仓
2025-06-21 08:00:03
硬刚到底,演出被举报后,曹云金再回应,没给郭德纲留"体面"

硬刚到底,演出被举报后,曹云金再回应,没给郭德纲留"体面"

阿废冷眼观察所
2026-11-18 15:51:55
马斯克遇上大麻烦!星链被欧洲围堵,欧洲为何对星链下手了?

马斯克遇上大麻烦!星链被欧洲围堵,欧洲为何对星链下手了?

普陀动物世界
2026-11-18 05:49:40
轰40+11+8三分,垫底鱼腩养出超级奇兵!末节独揽25分终结12连败

轰40+11+8三分,垫底鱼腩养出超级奇兵!末节独揽25分终结12连败

锅子篮球
2026-11-18 15:30:14
被丈夫满文军揭发,服刑11个月重获自由,李俐为何出狱7年才离婚

被丈夫满文军揭发,服刑11个月重获自由,李俐为何出狱7年才离婚

梦醉为红颜一笑
2026-11-17 18:57:37
特朗普不想在菲律宾浪费时间,他准备吃完午饭,专机立刻北上中国

特朗普不想在菲律宾浪费时间,他准备吃完午饭,专机立刻北上中国

铁锤侃侃而谈
2026-11-18 14:31:52
赵一鸣神话碎了:万店狂奔后,一场加盟商的集体出逃

赵一鸣神话碎了:万店狂奔后,一场加盟商的集体出逃

牛锅巴小钒
2026-11-17 02:38:22
41岁男子谎称37岁求职,遭拒后起诉被年龄歧视并索赔10万,法院判了

41岁男子谎称37岁求职,遭拒后起诉被年龄歧视并索赔10万,法院判了

极目新闻
2026-11-18 08:26:33
曝蔡天凤案大反转!6000W豪宅邝球拿了3000W,写他名是为了省税

曝蔡天凤案大反转!6000W豪宅邝球拿了3000W,写他名是为了省税

神秘的未知领域
2026-11-18 01:50:28
大逆转!湖人118-114击败黄蜂!东契奇创79年纪录,格兰姆斯爆砍47分

大逆转!湖人118-114击败黄蜂!东契奇创79年纪录,格兰姆斯爆砍47分

篮球大视野
2026-11-18 15:12:35
上层作孽,害死好人

上层作孽,害死好人

我是历史其实挺有趣
2025-08-02 08:59:00
2026-11-18 16:40:49
字节漫游指南
字节漫游指南
有态度网友ytd
234文章数 134关注度
往期回顾 全部

科技要闻

离职后首份声明!黄仁勋认错愿回归

头条要闻

特朗普启程前往深圳 登机前对着镜头做标志性挥拳动作

头条要闻

特朗普启程前往深圳 登机前对着镜头做标志性挥拳动作

体育要闻

“中超卡里乌斯”,成了大连的夺冠英雄

娱乐要闻

认亲被嘲讽后郭德纲委托律师发声明

财经要闻

黄仁勋留下的高分成绩单,能延续吗?

汽车要闻

全新红旗H9最有意思的地方,是它开始有自己的豪华逻辑了

态度原创

旅游
游戏
家居
房产
军事航空

旅游要闻

枣庄九星山“换秋装” 色彩斑斓美如画

《KALA》登陆Steam 在异变日常中追查主角过去

家居要闻

2026建博会(广州) 公装联探展交流活动

房产要闻

效果图曝光!三亚超级万象城,长这样!

军事要闻

美军再袭伊朗格什姆岛

无障碍浏览 进入关怀版