期待 PostgreSQL 19：查询提示

# 展望 Postgres 19：查询建议 来源：https://www.pgedge.com/blog/looking-forward-to-postgres-19-query-hints 好吧，世界末日终究还是来了。《捉鬼敢死队》里的彼得·文克曼说得没错，我们很快就要经历"人祭、猫狗同住、集体疯狂"了！大家散了吧，咱们这一趟跑得不错。Postgres 19 的功能冻结包含了一项*许多人曾断言永远不见天日的功能*：查询建议（query hints）。看来"永远别说永远"确实是句至理名言。 OK，严格来说它们并不叫"建议"。Postgres 社区可不会这么俗气。相反，Postgres 19 引入了两个新的 contrib 模块：`pg_plan_advice` (https://www.postgresql.org/docs/devel/pgplanadvice.html) 和 `pg_stash_advice` (https://www.postgresql.org/docs/devel/pgstashadvice.html)。这叫"计划建议"，您看。完全不同的东西。 如此重大的事件，光描述功能可不够。让我们从 Postgres 历史上最悠久的争论之一开始回顾。 ## "永不"的简史 Postgres 社区对查询建议的态度，可以说是*坚定*。官方关于此主题的 wiki 页面 (https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion) 明确写道： "我们对以其他数据库常见方式实现建议不感兴趣。基于'别人有所以我们也要有'的提议不会受欢迎。" 有道理。wiki 继续列出了六条建议会带来问题的充分理由： - 它们会造成维护噩梦。 - 它们会在升级时失效。 - 它们阻碍根本原因分析。 - 它们扩展性差。 - 优化器通常比你想象的更聪明。 - 它们实际上*阻碍了*规划器的改进，因为用户不再报告 bug。 没错，说得对。多年来，讨论就此结束。Postgres 不做建议。去修你的统计信息吧。下一个话题。 但在幕后，争论从未真正平息。早在 2010 年底，pgsql-performance (https://www.postgresql.org/list/pgsql-performance/) 邮件列表上爆发了一条传奇帖子 (https://www.postgresql.org/message-id/flat/4660.1297372332%40sss.pgh.pa.us)，持续数月，几乎涵盖了所有观点。帖子起初只是投诉 `COUNT(*)` 查询慢，然后转向 Oracle 对比，最后竟升级为一场关于 Postgres 是否需要建议的全面生存危机。 罗伯特·哈斯率先发难： "我认为说我们不想要建议是愚蠢的。我们想要建议，至少我们很多人想要。我们只是希望它们能真正工作，并且不烂。" 他进一步指出，Postgres 需要为 DBA 提供一个应对边缘情况的逃生舱口： "我们应该愿意为那些人提供一种方式，让他们在遇到那 0.1% 无法用现有方法修复的查询时不被解雇。" "请不要让人被解雇"这个论点很难反驳。 传奇人物汤姆·莱恩也表达了类似观点： "我还没见过不烂的建议方案……但我不说不存在。" 他实际上为此留下了一丝*缝隙*。 凯文·格里特纳后来指出了反建议论点中一个明显的悖论： "甚至那些表面上最反对建议的人，也曾表示他们宁愿优化器不要识别两个逻辑等价的结构并做相同优化，因为他们觉得当前的差异'有助于强制优化器'选择某个计划。这本质上就是在实现建议，只是拒绝文档化而已。" 他错了吗？我们中有多少人将 `enable_seqscan` 设为 `off` 来强制使用索引扫描？或者在子查询里塞一个 `OFFSET 0` 来防止规划器展平它？或者把物化 CTE 包裹在某个东西外面来创建优化围栏？这些除了名字不是建议，其他都是建议，只有经验丰富的专家才能使用这些粗糙的旋钮。我们只是在*假装*拒绝建议。 乔什·伯库斯，一位更直言不讳的反建议倡导者，划清了界限： "任何被编码到实际查询中的建议，日后都会变成巨大的维护和升级麻烦。" 但即使他也不反对*所有*形式的规划器覆盖。他的立场是反对 Oracle 那种嵌入 SQL 注释的方式。他偏好的层次结构是：先 GUC 成本参数，然后是数据库对象的成本参数，接着是新的统计元数据，查询建议只能作为最后绝望的手段。这实际上是个合理的行动计划，但 Postgres 一直缺少最后一个选项。 第三方扩展 pg_hint_plan (https://github.com/ossc-db/pg_hint_plan) 最终填补了空白，借用了 Oracle 基于注释的建议语法。它或多或少能工作，向核心添加建议的紧迫感悄然消散了。至少我们是这么认为的。 ## 换个名字 那么发生了什么变化？首先，构建这些模块的人正是罗伯特·哈斯。在那条史诗帖子十五年之后，他成为了 `pg_plan_advice` 和 `pg_stash_advice` 的作者。随你怎么说，但这人下的是盘大棋。 哈斯并没有简单地照搬 Oracle 风格的查询建议到 Postgres 上就完事。他从一开始就深深卷入了这场争议，深知所有痛点。因此，他一直在思考如何应对社区在过去二十年中提出的每一个反对意见。 伯库斯坚持认为建议绝不应存在于查询文本中。所以 `pg_plan_advice` 将建议*完全置于 SQL 之外*。建议通过 GUC（`pg_plan_advice.advice`）设置，或存储在由查询 ID 键控的独立 stash 中。查询本身不受额外内容的影响。 社区担心建议会完全取代规划器的判断，生成的计划看似"正确"但实际上灾难性。所以 `pg_plan_advice` 的工作方式是*约束*规划器的搜索空间，而非取代。文档明确指出：建议"只能产生核心规划器认为可行的计划"。建议只是*引导*规划器走向它已考虑过的方向。 当建议错误或过时时呢？规划器不会默默产生垃圾结果或抛出错误，而是将受影响的节点标记为 `Disabled`，并在剩余约束内回退到它认为最佳的计划。错误建议会优雅降级。 也许最好的一点是，系统会自己生成建议。你无需记忆语法参考并手工从头编写建议字符串。你可以询问规划器它在做什么，它会以一种同时作为语法的格式回应： ```sql CREATE TABLE my_fact ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, dim_id BIGINT NOT NULL ); CREATE TABLE my_dim ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, stuff TEXT ); CREATE INDEX idx_fact_dim_id ON my_fact (dim_id); EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM my_fact f JOIN my_dim d ON f.dim_id = d.id; QUERY PLAN ---------------------------------- Hash Join Hash Cond: (f.dim_id = d.id) -> Seq Scan on my_fact f -> Hash -> Seq Scan on my_dim d Generated Plan Advice: JOIN_ORDER(f d) HASH_JOIN(d) SEQ_SCAN(f d) NO_GATHER(f d) ``` 规划器直接交出了能重现其当前计划的建议字符串。接下来只需按需调整。只需将 `pg_plan_advice` 添加到 `shared_preload_libraries` 中，就能获得所有这些功能。 ## 轻声细语 对于社区抵制了几十年的东西来说，这种建议语言出乎意料地富有表现力。有几种类别，每种针对查询规划的不同方面。 **扫描方法**控制如何访问单个表： ```sql SET pg_plan_advice.advice = 'INDEX_SCAN(f idx_fact_dim_id)'; ``` 这告诉规划器对别名 `f` 使用特定的索引。文档还列出了其他几种方便的扫描类型。想要完全从最终计划中排除一个表？甚至还有 `DO_NOT_SCAN` 可以做到。 **连接顺序**是其中更有趣的部分。`JOIN_ORDER` 标签支持嵌套语法，圆括号表示严格顺序，花括号表示灵活分组： ```sql -- 严格顺序：先连接 b 和 c，然后将该结果与 a 连接，再与 d 连接 SET pg_plan_advice.advice = 'JOIN_ORDER(a (b c) d)'; -- 灵活分组：b 和 c 以任意顺序连接，但它们必须出现在 a 和 d 之间 SET pg_plan_advice.advice = 'JOIN_ORDER(a {b c} d)'; ``` 这里的语法很棒，在必要顺序和可选顺序之间提供了方便的区分。这使得你可以锁定连接顺序的关键部分，同时给规划器留出优化其余部分的空间。认为 DBA 在某些情况下比规划器更懂的论点，在连接顺序上绝对成立，现在它也可调了。 **连接方法**控制规划器对每个连接使用的算法，有多种选项。这里有一个微妙但重要的细节：指定一种方法意味着目标应该出现在该连接类型的*内层*。因此 `HASH_JOIN(d)` 意味着"从 d 构建哈希表"。文档有助于消除歧义，而且这仍然很新，有待进一步澄清。 想要指定多个关系应该出现在*同一个*连接的内层？将它们括在圆括号中： ```sql -- d1 和 d2 各自在单独的哈希连接的内层 SET pg_plan_advice.advice = 'HASH_JOIN(d1 d2)'; -- d1 和 d2 一起在同一个哈希连接的内层 SET pg_plan_advice.advice = 'HASH_JOIN((d1 d2))'; ``` **并行查询控制**用 `GATHER`、`GATHER_MERGE` 和 `NO_GATHER` 来控制并行执行的位置和是否发生，使功能更加完善。 我们可以组合所有这些吗？当然可以： ```sql SET pg_plan_advice.advice = 'JOIN_ORDER(f d1 d2) HASH_JOIN(d1 d2) SEQ_SCAN(f) INDEX_SCAN(d1 idx_d1_pk)'; ``` 这是一个字符串，同时控制连接顺序、连接方法和扫描策略。记住，如果建议的任何部分无法被满足，规划器会优雅降级，而不是着火、烧毁然后掉进沼泽。 ## 暂存以备后用 直接使用 `pg_plan_advice.advice` 对于临时调优和实验来说很棒，但生产环境呢？每次查询前都设置一个 GUC 很不方便。这就是 pg_stash_advice (https://www.postgresql.org/docs/devel/pgstashadvice.html) 的用武之地。 一旦我们将它添加到 `shared_preload_libraries` 中，还需要安装它： ```sql CREATE EXTENSION pg_stash_advice; ``` 该系统通过建立一个"建议暂存区"（advice stash）来工作，这是一个存储在共享内存中的命名集合，包含查询到建议的映射。创建暂存区，用查询 ID 键控的建议字符串填充它，并告诉会话在规划时查阅它： ```sql -- 创建一个暂存区 SELECT pg_create_advice_stash('production_tuning'); -- 从 EXPLAIN VERBOSE 或 pg_stat_statements 获取查询 ID EXPLAIN (VERBOSE, PLAN_ADVICE) SELECT * FROM my_fact f JOIN my_dim d ON f.dim_id = d.id; -- 为该查询暂存建议 SELECT pg_set_stashed_advice( 'production_tuning', 5424487836266966148, 'INDEX_SCAN(f idx_fact_dim_id) NESTED_LOOP_PLAIN(f)' ); -- 为此会话激活暂存区 SET pg_stash_advice.stash_name = 'production_tuning'; ``` 从此刻起，每当规划器遇到这个特定的查询模式时，它会自动应用暂存的建议。而且由于它没有嵌入到查询本身中，我们可以随时更改它。不用担心查询 ID 会变，它本质上是查询的哈希值（不包含任何动态参数）；建议应该普遍适用。 暂存区可以按会话、按角色或按数据库设置作用域： ```sql -- 该数据库中的每个会话都使用此暂存区 ALTER DATABASE mydb SET pg_stash_advice.stash_name = 'production_tuning'; -- 只有该角色使用此暂存区 ALTER ROLE reporting_user SET pg_stash_advice.stash_name = 'reporting_tuning'; ``` 默认情况下，暂存区通过 `pg_stash_advice.persist = on` 持久化到磁盘，带有可配置的写入间隔。这意味着它们能在重启后存活，允许 DBA 检查其内容、更新单个条目或在不必要时删除整个暂存区。 这部分满足了生产 DBA 的实际需求。在遇到有问题的查询且无法更改应用程序代码的情况下，有时重定向规划器的能力至关重要。因此创建一个暂存区，放入正确的建议，问题就解决了，无需改动一行 SQL。当统计信息、应用程序或规划器本身改进后，只需在数据库端删除计划建议即可。应用程序或最终用户永远无需知道。 建议暂存区是最后的手段，并且被设计为临时使用。 ## 信任但验证 更周到的设计决策之一是反馈机制。`EXPLAIN` 清楚地说明了每条建议的结果。考虑这个使用之前事实表和维度表的设置： ```sql SET pg_plan_advice.advice = $$ INDEX_SCAN(f idx_fact_dim_id) NESTED_LOOP_PLAIN(f) INDEX_SCAN(d no_such_index) SEQ_SCAN(z) SEQ_SCAN(f) $$; EXPLAIN (COSTS OFF, VERBOSE, PLAN_ADVICE) SELECT * FROM my_fact f JOIN my_dim d ON f.dim_id = d.id; ``` `EXPLAIN` 输出对我们的建议提供了无可挑剔的反馈： ``` Supplied Plan Advice: INDEX_SCAN(f idx_fact_dim_id) /* matched */ INDEX_SCAN(d no_such_index) /* matched, inapplicable, failed */ SEQ_SCAN(z) /* not matched */ SEQ_SCAN(f) /* matched, conflicting, failed */ NESTED_LOOP_PLAIN(f) /* matched */ ``` 从中我们可以看出几点： - `matched` 表示找到了目标，建议已应用。 - `not matched` 表示查询中根本没有找到目标。 - `inapplicable` 表示建议无法满足（比如引用了不存在的索引）。 - `failed` 表示最终计划不符合建议。 - `conflicting` 表示两条建议相互矛盾。 - 建议支持美元 `$$` 引用语法，使其更方便且更易于阅读。 也可以设置 `pg_plan_advice.feedback_warnings = true`，在查询执行期间将这些作为 `WARNING` 消息输出。这对于在生产日志中及时发现过时建议很有用。 那么它*不能*做什么？目前，无法控制聚合是使用排序还是哈希。也无法为 `UNION` 或 `INTERSECT` 操作提供策略。规划器还可以基于正确性原因拒绝建议。应用建议也有轻微的性能损失，因为它必然会中断规划器循环，即使最终计划未改变。文档明确警告要谨慎使用，这是有原因的。 它是一把手术刀，请当作手术刀来使用。 ## 长期博弈 这个故事有某种深层次的满足感。不知何故，查询建议从一个我们可能永远都不会有的公认功能，变成了现有最好的实现之一。开发人员还以合理的方式解决了关于建议的主要抱怨： - 建议位于 SQL 之外，避免永久嵌入。 - 建议引导规划器而非替代它。 - 建议优雅降级并附带详细反馈，避免最坏情况计划。 - 规划器生成自己的建议字符串以自我文档化，便于微调。 社区曾表示，如果有人能避免其他系统中观察到的问题，他们会考虑建议。花了十五年的时间，但终于有人做到了。不管这是固执还是极高的标准，这就是 Postgres 的方式™。 这会结束争论吗？也许不会。总有一些人坚称规划器应该