pg_plan_advice —— 帮助规划器获得正确的执行计划

# F.30. pg_plan_advice — 帮助规划器获得正确计划 原文来源：https://www.postgresql.org/docs/19/pgplanadvice.html 本文档适用于不受支持的 PostgreSQL 版本。您可能希望查看当前版本（https://www.postgresql.org/docs/current/pgplanadvice.html）的相同页面，或上面列出的其他受支持版本之一。 `pg_plan_advice` 模块允许使用一种专用的“计划建议”小语言来描述、重现和更改关键的规划器决策。它的目的是让用户能够稳定他们认为好的计划选择，并实验规划器认为非最优的计划。 请注意，由于规划器通常能做出好的决策，覆盖其判断很容易适得其反。例如，如果底层数据的分布发生变化，规划器通常可以选择调整计划以保持良好性能。如果计划建议阻止了这种调整，可能会选择非常糟糕的计划。只有在约束规划器选择的风险被收益所抵消时，才使用计划建议，这一点非常重要。 ### F.30.1. 入门指南\# (https://www.postgresql.org/docs/19/pgplanadvice.html#PGPLANADVICE-GETTING-STARTED) 首先，您必须安排加载 `pg_plan_advice` 模块。您可以通过将 `pg_plan_advice` 添加到 `shared_preload_libraries` (https://www.postgresql.org/docs/19/runtime-config-client.html#GUC-SHARED-PRELOAD-LIBRARIES) 并重新启动服务器来实现系统级加载；或者将其添加到 `session_preload_libraries` (https://www.postgresql.org/docs/19/runtime-config-client.html#GUC-SESSION-PRELOAD-LIBRARIES) 并启动新会话；或者使用 `LOAD` (https://www.postgresql.org/docs/19/sql-load.html) 命令将其加载到单个会话中。 一旦 `pg_plan_advice` 模块加载完成，`EXPLAIN` (https://www.postgresql.org/docs/19/sql-explain.html) 将支持 `PLAN_ADVICE` 选项。您可以使用此选项查看所选计划的计划建议字符串。例如： `` EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id; QUERY PLAN ------------------------------------ Hash Join Hash Cond: (f.dim_id = d.id) -> Seq Scan on join_fact f -> Hash -> Seq Scan on join_dim d Generated Plan Advice: JOIN_ORDER(f d) HASH_JOIN(d) SEQ_SCAN(f d) NO_GATHER(f d) `` 在此示例中，用户未指定任何建议；相反，规划器被允许做出它认为最好的任何决策，这些决策以建议字符串的形式被记录。`JOIN_ORDER(f d)` 表示 `f` 应该是驱动表，并且第一个要连接的表是 `d`。`HASH_JOIN(d)` 表示 `d` 应该出现在哈希连接的内部。`SEQ_SCAN(f d)` 表示 `f` 和 `d` 都应通过顺序扫描访问。`NO_GATHER(f d)` 表示 `f` 和 `d` 都不应出现在 `Gather` 或 `Gather Merge` 节点下方。有关计划建议小语言的更多详细信息，请参阅下面的建议目标（https://www.postgresql.org/docs/19/pgplanadvice.html#PGPLANADVICE-TARGETS）和建议标签（https://www.postgresql.org/docs/19/pgplanadvice.html#PGPLANADVICE-TAGS）信息。 一旦您获得查询的建议字符串，就可以用它来控制该查询的规划方式。您可以通过将 `pg_plan_advice.advice` 设置为您选择的建议字符串来实现。这可以是系统生成的建议字符串，也可以是您自己编写的。创建自己的建议字符串的一个好方法是获取系统生成的字符串，并挑选出您希望强制执行的元素。在上面的示例中，`pg_plan_advice` 生成了关于连接顺序、连接方法、扫描方法和并行性使用的建议，但您可能只想控制连接顺序： `` SET pg_plan_advice.advice = 'JOIN_ORDER(f d)'; EXPLAIN (COSTS OFF) SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id; QUERY PLAN ------------------------------------ Hash Join Hash Cond: (f.dim_id = d.id) -> Seq Scan on join_fact f -> Hash -> Seq Scan on join_dim d Supplied Plan Advice: JOIN_ORDER(f d) /* matched */ `` 由于未指定 `PLAN_ADVICE` 选项，因此计划未生成建议字符串。但提供的计划建议仍然会显示，以便查看 `EXPLAIN` 输出的人知道所选计划受到计划建议的影响。如果不需要显示提供的计划建议信息，可以通过配置 `pg_plan_advice.always_explain_supplied_advice = false` 来抑制。对于每条提供的建议，输出会显示建议反馈（https://www.postgresql.org/docs/19/pgplanadvice.html#PGPLANADVICE-FEEDBACK），指示建议是否成功应用于查询。在这个例子中，反馈显示 `/* matched */`，这意味着在查询中找到了 `f` 和 `d`，并且生成的查询计划符合指定的建议。 ### F.30.2. 工作原理\# (https://www.postgresql.org/docs/19/pgplanadvice.html#PGPLANADVICE-HOW-IT-WORKS) 计划建议是命令式的；也就是说，它指定应该做什么。然而，在实现层面，`pg_plan_advice` 通过告诉核心规划器不应该做什么来工作。换句话说，它通过约束规划器的选择来运作，而不是替换它。因此，无论您提供什么建议，您都只会得到核心规划器本会为查询考虑的计划。如果您通过提供建议字符串试图强制您认为正确的计划，而规划器仍然无法生成所需的计划，这意味着要么您的建议字符串有 bug，要么该计划未被核心规划器视为可行。这种情况通常由两个原因之一导致。首先，可能是规划器认为您试图强制的计划在语义上不正确——也就是说，它会产生错误的结果——因此未被考虑。其次，可能是规划器基于成本以外的某些原因拒绝了您希望生成的计划。例如，对于一个非常简单的查询，如 `SELECT * FROM some_table`，查询规划器会在执行任何成本计算之前决定使用索引毫无价值。即使您设置 `enable_seqscan = false`，也无法强制它为此查询使用索引，您也无法通过计划建议强制使用索引。 指定计划建议不应导致规划器失败。但是，如果您指定了要求某些不可能事项的计划建议，您可能会在 `EXPLAIN` 输出中看到某些计划节点被标记为 `Disabled: true`。在某些情况下，这样的计划基本上与您未提供任何建议时获得的计划相同，但在其他情况下，它们可能会糟糕得多。例如： `` SET pg_plan_advice.advice = 'JOIN_ORDER(x f d)'; EXPLAIN (COSTS OFF) SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id; QUERY PLAN ---------------------------------------------------- Nested Loop Disabled: true -> Seq Scan on join_fact f -> Index Scan using join_dim_pkey on join_dim d Index Cond: (id = f.dim_id) Supplied Plan Advice: JOIN_ORDER(x f d) /* partially matched */ `` 由于 `f` 和 `d` 都不是 `JOIN_ORDER()` 规范中的第一个表，规划器认为应该先与 `x` 进行连接，因此禁用了它们之间的所有直接连接。由于规划不允许失败，最终仍然选择了两个关系之间的一个禁用计划，但这里是一个 `Nested Loop`，而不是上面未指定建议示例中选择的 `Hash Join`。这种事情可能以多种不同方式发生；当发生时，结果计划通常比未指定任何建议时要糟糕。因此，最好验证您指定的建议适用于应用它的查询，并且结果符合预期。 ### F.30.3. 建议目标\# (https://www.postgresql.org/docs/19/pgplanadvice.html#PGPLANADVICE-TARGETS) *建议目标*唯一标识特定查询中特定关系的特定实例。在简单情况下，如上述示例，建议目标就是关系别名。然而，当使用子查询、表被分区，或同一子查询中多次提及同一关系别名时（例如，`(foo JOIN bar ON foo.a = bar.a) x JOIN foo ON x.b = foo.b`），则需要更复杂的语法。这三种情况可能同时发生：一个关系可能被多次提及、被分区，并在子查询中使用。 因此，关系标识符的通用语法是： `` alias_name#occurrence_number/partition_schema.partition_name@plan_name `` 除 `alias_name` 外的所有组件都是可选的，且仅在需要时包含。当省略组件时，前面的标点也必须省略。对于给定子查询中关系的第一处出现，生成的建议会省略出现次数，但如果需要，写成 `#1` 也是合法的。分区模式（schema）和分区名称仅用于分区表的子表。在生成的建议中，`pg_plan_advice` 始终会包含这两者，但省略模式是合法的。对于顶层计划，计划名称被省略；对于任何子计划，必须包含计划名称。 通过检查查询来确定正确的建议目标并不总是容易。例如，如果规划器将子查询上拉至父查询级别，子查询内部的所有内容都会成为父查询级别的一部分，并使用父查询的子计划名称（如果上拉到顶层，则不使用子计划名称）。此外，正确的子查询名称有时并不明显。例如，当两个查询通过 `UNION` 或 `INTERSECT` 等操作连接时，SQL 语法中不存在子查询的名称；相反，系统会为每个分支分配一个系统生成的名称。发现正确建议目标的最简单方法是使用 `EXPLAIN (PLAN_ADVICE)` 并检查生成的建议。 ### F.30.5. 建议反馈\# (https://www.postgresql.org/docs/19/pgplanadvice.html#PGPLANADVICE-FEEDBACK) `EXPLAIN` 会以对每条提供建议添加注释的形式，提供关于提供的建议是否成功应用于查询的反馈。例如： `` SET pg_plan_advice.advice = 'hash_join(f g) join_order(f g) index_scan(f no_such_index)'; EXPLAIN (COSTS OFF) SELECT * FROM jo_fact f LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id WHERE val1 = 1 AND val2 = 1; QUERY PLAN ------------------------------------------------------------------- Hash Join Hash Cond: ((d1.id = f.dim1_id) AND (d2.id = f.dim2_id)) -> Nested Loop -> Seq Scan on jo_dim2 d2 Filter: (val2 = 1) -> Materialize -> Seq Scan on jo_dim1 d1 Filter: (val1 = 1) -> Hash -> Seq Scan on jo_fact f Supplied Plan Advice: INDEX_SCAN(f no_such_index) /* matched, inapplicable, failed */ HASH_JOIN(f) /* matched */ HASH_JOIN(g) /* not matched */ JOIN_ORDER(f g) /* partially matched */ `` 对于这个查询，`f` 是有效的建议目标，但 `g` 不是。因此，将 `f` 放在哈希连接内部的请求被列为 `matched`，但将 `g` 放在哈希连接内部的请求被列为 `not matched`。`JOIN_ORDER` 建议标签涉及一个有效目标和一个无效目标，因此被列为 `partially matched`。注意，`HASH_JOIN(f g)` 实际上是对两种逻辑上独立行为的请求，因此在反馈中被分解为 `HASH_JOIN(f)` 和 `HASH_JOIN(g)`。相比之下，`JOIN_ORDER(f g)` 是一个单一请求，并保持原样显示。 建议反馈可以包含以下任何一项： - `matched` 表示在查询规划过程中，所有指定的建议目标都被同时观察到，且建议可以被强制执行。 - `partially matched` 表示在查询规划过程中，观察到部分但非全部指定的建议目标，或者所有建议目标都被观察到但并非同时发生。例如，如果 `JOIN_ORDER` 建议的所有目标单独匹配查询，但建议的连接顺序不合法，就可能发生这种情况。 - `not matched` 表示在查询规划过程中，未观察到任何指定的建议目标。这可能是因为建议根本不匹配查询，也可能是因为查询的相关部分未被规划，例如由于被简化为常量 false 的条件所阻挡。 - `inapplicable` 表示由于某种原因，建议标签无法应用于建议目标。例如，如果请求使用不存在的索引，或者尝试控制非半连接的半连接唯一性，就会发生这种情况。 - `conflicting` 表示两条或更多建议请求不兼容的行为。例如，如果您对同一表建议顺序扫描和索引扫描，两个请求都会被标记为冲突。如果连接方法建议或半连接唯一性建议暗示了与显式指定的连接顺序不兼容的连接顺序，也会经常发生这种情况；请参见第 F.30.4.3 节 (https://www.postgresql.org/docs/19/pgplanadvice.html#PGPLANADVICE-JOIN-METHOD)。 - `failed` 表示查询计划不遵守建议。这仅发生在还被标记为 `matched` 的条目上。它经常发生在还被标记为 `conflicting` 或 `inapplicable` 的条目上。然而，也可能发生在建议在 `pg_plan_advice` 能够确定的范围内是有效的，但规划器无法构建一个遵守建议的合法计划。重要的是要注意，`pg_plan_advice` 执行的健全性检查相当肤浅，主要关注建议字符串中的逻辑不一致；只有规划器才知道什么实际可行。 所有建议都应被精确地标记为 `matched`、`partially matched` 或 `not matched` 之一。 ### F.30.6. 配置参数\# (https://www.postgresql.org/docs/19/pgplanadvice.html#PGPLANADVICE-CONFIG-PARAMS) `pg_plan_advice.advice` (`string`) `pg_plan_advice.advice` 是在查询规划过程中使用的建议字符串。 `pg_plan_advice.always_explain_supplied_advice` (`boolean`) `pg_plan_advice.always_explain_supplied_advice` 使 `EXPLAIN` 始终显示任何提供的建议以及相关的建议反馈（https://www.postgresql.org/docs/19/pgplanadvice.html#PGPLANADVICE-FEEDBACK）。默认值为 `true`。如果设置为 `false`，则仅在使用 `EXPLAIN (PLAN_ADVICE)` 时才显示此信息。 `pg_plan_advice.always_store_advice_details` (`boolean`) `pg_plan_advice.always_store_advice_details` 允许 `EXPLAIN` 在即使使用预备查询时也显示与计划建议相关的详细信息。默认值为 `false`。规划预备查询时，无法知道 `EXPLAIN` 是否稍后会被使用，因此默认情况下，为了减少开销，`pg_plan_advice` 不会生成计划建议或对提供建议的反馈。这意味着如果在预备查询上使用 `EXPLAIN EXECUTE`，它将无法显示这些信息。将此设置更改为 `true` 可避免此问题，但会增加额外开销。最好仅在需要它的会话中启用此选项，而不是系统范围启用。 `pg_pl