如果您要从 Oracle 数据库迁移到 PostgreSQL，您可能习惯于使用提示来优化查询。在 Oracle 中，这些是嵌入在 SQL 中的特殊指令（如/*+ INDEX(...) */），用于指导优化器的执行计划。它们可能非常有用，但随着时间的推移也会引入复杂性和“提示债务”。

PostgreSQL 采用了一种截然不同的查询优化方法。Postgres 社区历来强调依靠基于成本的规划器来根据统计数据、索引和配置参数选择执行计划，而不是支持内置提示。在实践中，这种方法很多时候都有效，但有时规划器会很固执，不断选择错误的计划。在迁移情况下，这尤其复杂，因为性能可能取决于之前使用 Oracle 提示指定的特定执行计划。

因此，您可能会问自己： 迁移到 Postgres 时如何复制或替换 Oracle 提示？这就是 pg_hint_plan扩展的作用所在。

在这篇文章中，我们将探讨 Oracle 的提示系统和 PostgreSQL 的规划器与 pg_hint_plan 之间的区别，讨论在 Postgres 查询中何时仍然需要提示，并介绍有效使用 pg_hint_plan 的最佳实践，包括 pganalyze 如何提供帮助。

何时（以及何时不）使用提示

将所有 Oracle 提示迁移到 pg_hint_plan 可能很诱人，但在 PostgreSQL 中，这可能有点过头，有时甚至适得其反。让我们来谈谈提示在经过良好调整的 Postgres 环境中的适用之处

依靠 PostgreSQL 基于成本的规划器

PostgreSQL 是围绕基于成本的规划器构建的，该规划器通常无需人工干预即可选择有效的执行路径。它使用：

表大小、列数据分布等的统计信息。
规划器成本设置如 random_page_cost和 cpu_tuple_cost
服务器配置参数，例如 enable_seqscan、 work_mem和 effective_cache_size

PostgreSQL 规划器背后的哲学是，如果您的统计数据、索引和成本参数经过精心调整，引擎通常可以自行找出最佳计划，并且很少需要依赖提示。

但是，这个系统并不完美，Postgres 有时会选择次优计划，正如我们在 Postgres规划器怪癖系列中所讨论的那样。

Postgres 规划器问题的根本原因

Postgres 查询计划的一个常见问题是过时或不正确的统计数据。有关表列的统计数据和查询过滤器的选择性对于规划器做出正确的决策至关重要ANALYZE。频繁的操作与调整后的统计目标设置和使用相结合 CREATE STATISTICS，可确保系统捕获有关数据分布的当前信息。

精心设计的模式、精心选择的索引以及适当时的表分区通常可以比手动提示提供更大的性能提升，因为手动提示在大型表上只能起到有限的作用。

work_mem、 random_page_cost和等设置 effective_cache_size对规划器做出的决策有重大影响，但它们通常设置为默认值，这可能会导致糟糕的查询计划。优化这些设置可以解决许多查询性能挑战，而无需引入提示。当规划器的成本模型与硬件和数据的实际情况很好地匹配时，它通常会得出更好的计划。

当提示有帮助时

尽管 PostgreSQL 的规划器功能强大，但有时提示也会很有用。事实上，强制执行某个计划进行调试可以提供有价值的见解，了解为什么规划器的默认选择可能不太理想，以及查询计划的哪一部分成本不准确（通常是由统计问题引起的）。

传统的 Oracle 查询通常严重依赖提示，调整提示或重构架构可能风险太大或耗时太长。在这种情况下， pg_hint_plan可以复制 Oracle 中的特定行为，而无需完全重写。提示还有助于解决高度复杂的查询或异常的数据分布，这些分布始终会导致规划器误入歧途。它们同样可以用作临时补丁，同时解决更深层次的问题，例如缺少统计数据或参数设置不正确。

当 Postgres 中正确处理统计准确性、模式设计和参数调整时，提示将成为额外的复杂性，而不是必需品。谨慎使用它们，专注于真正需要硬编码逻辑的特殊情况。

将 Oracle 提示映射到 pg_hint_plan

Oracle 提示和 pg_hint_plan 提示均使用嵌入到 SQL 语句中/*+ ... */。它们可以：

强制使用特定索引或连接方法（例如嵌套循环）
启用或禁用并行执行
覆盖其他计划选择

这些提示可能非常直接：“在此表上使用索引 X”或“使用嵌套循环连接来连接表 A 和 B”。当数据库优化器无法自行选择最佳计划或您需要跨不同实例保持一致的性能时，这种级别的控制有时至关重要。

当您决定在 Postgres 中复制 Oracle 提示时，您可能会寻找直接等效项。pg_hint_plan 支持许多（但不是全部）类似 Oracle 的提示。pg_hint_plan 主要控制扫描方法、连接方法、连接顺序和查询并行性。Oracle 的许多用于重写查询、星型转换、动态采样和专用缓存的高级提示在 Postgres 中根本无法使用或不适用。

相反，在 Postgres 中，您通常通过调整规划器 GUC（如 enable_hashjoin、 enable_nestloop）、重写查询、使用 CTE 关键字实现查询的部分内容MATERIALIZED或使用推动 Postgres 规划器的索引/约束来实现类似的行为。

让我们回顾一些常见的情况，并将它们从 Oracle 数据库提示映射到 pg_hint_plan 语法或其他 Postgres 替代方案。

访问路径（或索引）提示

预言提示	pg_hint_plan 等效项	笔记
`FULL(table)` 强制全表扫描	`SeqScan(table)`	强制 Postgres 对命名表使用顺序扫描（在 Oracle 上称为全表扫描）。
`INDEX(table [index])` 强制索引扫描	`IndexScan(table [index])` 或者 `IndexOnlyScan(table [index])` 或者 `BitmapScan(table [index])`	pg_hint_plan 对常规索引扫描、仅索引扫描或位图索引扫描有单独的提示。
`INDEX_FFS(table index)` 快速全索引扫描	没有直接等价物。 `IndexOnlyScan`是近似的。	如果所有筛选和返回的列都已编入索引，Postgres 可以使用 IndexOnlyScan 从索引中回答查询。但是，Postgres 有时仍会检查表以验证已删除行的可见性（此功能无法关闭）。
`INDEX_DESC(table [index])` 反向索引扫描	`IndexScan` `ORDER BY ... DESC`在查询本身中有一个。	pg_hint_plan 不能直接强制执行降序索引扫描；您通常依赖查询顺序或具有正确排序顺序的索引。
`NO_INDEX(table [index])` 禁止索引	无同等物。	没有相当于禁止单个索引的操作。
`INDEX_JOIN(table)` 使用索引连接	无同等物。	PostgreSQL 没有像 Oracle 那样的直接的“索引连接”概念。

在 Oracle 中，您可能有：

SELECT /*+ INDEX(table1 idx_table1_col) */ 
       col1, col2
FROM   table1
WHERE  col1 = 'something'
ORDER BY col2 LIMIT 1;

在带有 pg_hint_plan 的 PostgreSQL 中，您可以将其转换为：

/*+
  IndexScan(table1 idx_table1_col)
*/
SELECT col1, col2
FROM   table1
WHERE  col1 = 'something'
ORDER BY col2 LIMIT 1;

连接操作提示

预言提示	pg_hint_plan 等效项	笔记
`USE_NL(table1 table2)` 使用嵌套循环	`NestLoop(table1 table2)`	强制两个命名表之间进行嵌套循环连接。
`USE_HASH(table1 table2)` 使用哈希连接	`HashJoin(table1 table2)`	强制两个命名表之间进行哈希连接。
`USE_MERGE(table1 table2)` 使用排序合并连接	`MergeJoin(table1 table2)`	强制在两个命名表之间进行合并连接。
`USE_NL_WITH_INDEX(t1 idx1)`	`NestLoop(table1 table2)`+ `IndexScan(table1 index1)`+ `Leading((table2 table1))`	为了执行 Postgres 所称的参数化索引扫描，提示必须强制 NestedLoop、连接顺序（通过 Leading）和使用正确的索引。请注意，Leading 提示需要使用额外的括号来强制排序。列出的第一个表是外表，然后是内表（即索引扫描所在的表）。
`NO_USE_NL(t1 [t2...])` `NO_USE_MERGE(t1 [t2...])` `NO_USE_HASH(t1 [t2...])`	`NoNestLoop(t1 t2 [t3...])` `NoMergeJoin(t1 t2 [t3...])` `NoHashJoin(t1 t2 [t3...])`	pg_hint_plans 指示 PostgreSQL 的查询规划器不要对列出的表（需要包含内表和外表）使用嵌套循环/合并/哈希连接，而 Oracle 提示告诉优化器不要对每个指定的表（其中它是连接的内部表）使用嵌套循环/合并/哈希连接。

加入顺序提示

预言提示	pg_hint_plan 等效项	笔记
`ORDERED` 按 FROM 子句中的表顺序进行连接	`Set(join_collapse_limit 1)`	在 Postgres 中，将设置 `join_collapse_limit`设为“1”将强制 Postgres 按照查询中列出的顺序连接表。您可以 `SET`在运行查询之前通过 pg_hint_plan 或常规命令设置此项。请参阅Postgres 文档中的示例。
`LEADING(t1 t2 ... tN)`	`Leading(t1 t2 ... tN)` `Leading(((t1 t2) t3))`	pg_hint_plan 支持 `Leading(...)`修复连接顺序。您可以按照所需的连接顺序列出多个表。使用语法并在每对周围添加附加括号来指定哪个表用作内表或外表。

并行/并行度提示

预言提示	pg_hint_plan 等效项	笔记
`PARALLEL(table, n)` 平行度 n	`Parallel(table n hard)`	pg_hint_plan 默认（“软”）仅设置配置的最大工作线程数（ `max_parallel_workers_per_gather`），但如果成本不利于它，则不会强制执行并行计划。您可以通过将第三个参数指定为来强制执行并行计划 `hard`，这与 Oracle 在指定特定并行度时的行为一致。
`NO_PARALLEL(table)` 禁止并行	`Parallel(table 0)`	当表值设置为零时，pg_hint_plan 会抑制并行执行。

pg_hint_plan中的示例用法，将并行工作者从默认值 2（max_parallel_workers_per_gather）增加到 4，仅用于此查询对“sales”表的使用：

/*+
  Parallel(sales 4)
*/
SELECT ...

查询转换和子查询提示

Oracle 有许多控制查询转换的提示（例如取消嵌套子查询、合并视图、星型转换等）。pg_hint_plan 没有提供这些转换的直接等效项；PostgreSQL 的规划器转换通常不是基于提示的，而是由自动控制或由 GUC 参数控制。

预言提示	pg_hint_plan 等效项	笔记
`UNNEST / NO_UNNEST`	没有任何	PostgreSQL 会自动决定子查询是否嵌套（横向连接、子查询展平等），而 pg_hint_plan 无法影响这一点。但是，可以使用带有关键字的 CTE 重写查询 `NOT MATERIALIZED`，其行为类似于 Oracle 的 `UNNEST`，或者 `MATERIALIZED`其行为类似于 `NO_UNNEST`。请参阅 Postgres 文档。
`MERGE`/ `NO_MERGE`	没有任何	在 Postgres 中，视图会自动内联，就像它们是子查询一样；没有细粒度的提示来控制这一点。
`PUSH_SUBQ`/ `NO_PUSH_SUBQ`	没有任何	无法直接控制子查询的执行 `pg_hint_plan`。
`STAR_TRANSFORMATION`/ `NO_STAR_TRANSFORMATION`	没有任何	Oracle 针对数据仓库模式的星型转换在 Postgres 中没有直接对应项。
`FACT`/ `NO_FACT`	没有任何	Oracle 将这些用于星型模式；不适用于 Postgres。

结果缓存和其他专门提示

预言提示	pg_hint_plan 等效项	笔记
`RESULT_CACHE`/ `NO_RESULT_CACHE`	没有任何	PostgreSQL 没有像 Oracle 那样的内置查询结果缓存。
`OPT_PARAM(...)`	`Set(...)`	Postgres 参数通常在会话级别设置（“SET”命令）或通过 pg_hint_plan 中的“Set”提示设置。请注意，Oracle 和 Postgres 之间可以设置的参数有所不同。
`DYNAMIC_SAMPLING(...)`	没有任何	Postgres 统计系统基于查询执行之外的表的单独分析来工作，并且不具有动态采样的等效性。
`QB_NAME`	没有任何	pg_hint_plan 不提供与 Oracle 的查询块功能等效的提示。
`PUSH_PRED`/ `NO_PUSH_PRED`	没有任何	Postgres 根据子查询的启发式方法自动处理谓词下推；没有直接提示。
`USE_CONCAT`	没有任何	Oracle 使用它来强制将 `OR`子句扩展为 `UNION ALL`查询。Postgres 不支持自动执行此转换，需要手动重写查询。
`NO_QUERY_TRANSFORMATION`	没有任何	Postgres 在规划过程中的转换不能通过提示关闭或修改。

额外的 pg_hint_plan 功能（没有 Oracle 等效功能）

pg_hint_plan 具有额外的提示，这些提示不映射到 Oracle 提示，但可能会有所帮助：

Rows(table1 table2 [ n ])table1 and table 2：告诉规划器假设返回行之间存在连接n（替换或调整统计得出的估计值），影响连接顺序和计划选择。
Memoize(table1 table2)/ NoMemoize(table1 table2)：影响是否将 Memoize 功能应用于给定的连接表。Memoize 有时会导致 Postgres 规划器成本下降，因此“NoMemoize”提示可用于避免可能偏向嵌套循环连接的查询计划。

调试 pg_hint_plan 提示的最佳实践

有时 pg_hint_plan 提示不会生效，但原因并不总是很清楚，因为即使 pg_hint_plan 提示没有生效，Postgres 也总是会为您提供计划。

最常见的问题可能是：

指定多个提示注释（如果有多个提示，则必须在一个/*+ ... */注释中全部指定它们）
使用不正确的 pg_hint_plan 语法（例如NestedLoop代替NestLoop）
规划器没有可行的路径来使用提示（例如，因为请求的索引不能用于给定的表达式）
查询中重复使用的表名没有唯一的别名（在这种情况下，您需要为每个表分配一个别名）
分区表的提示必须针对分区表父级，而不是子级
没有指定名称的子查询（即不是 CTE）只能在某些情况下提示

但是，默认情况下您可能看不到任何明显的问题迹象，因为 pg_hint_plan 默认不显示任何调试输出。

为了更好地理解为什么可能没有使用提示，您可以启用该 pg_hint_plan.print_debug标志。这将为您提供如下输出：

SET pg_hint_plan.debug_print = true;  
/*+ NestedLoop(table1 table2) */ EXPLAIN SELECT * FROM …;

INFO:  pg_hint_plan: hint syntax error at or near "NestedLoop".  
DETAIL:  Unrecognized hint keyword "NestedLoop".  
                                          QUERY PLAN                                             
----------------------------------------------------------------------------------------------------  
…

此外，您可以通过将客户端日志级别（ client_min_messages）提升至来显示有关提示使用情况的更详细输出LOG，这将告诉您哪些提示被成功使用：

SET client_min_messages = LOG;
/*+ NestLoop(table1 table2) IndexScan(table3) */ EXPLAIN SELECT * FROM table1 JOIN table2 
ON (table2_id = table2.id) WHERE table1_id = '123';

LOG:  pg_hint_plan:
used hint:
NestLoop(table1 table2)
not used hint:
IndexScan(table3)
duplication hint:
error hint:
                                        QUERY PLAN                                        
----------------------------------------------------------------------------------------------
...

您可以在pg_hint_plan 文档中找到需要考虑的其他方面。

使用 pganalyze 测试查询提示

通常在完成生产前性能测试的最后期限或上线后，Oracle 到 Postgres 的迁移会遇到挑战。在这种情况下，pganalyze 可以帮助您使用查询优化工作簿快速迭代不同的提示和基准查询计划。

在以下示例中，我们将基线查询与使用 pg_hint_plan 选择特定索引的查询变体进行了比较。从这些结果可以看出，实施提示可将性能提高 60% 以上，并且整个团队都可以记录下来，以了解进行更改的原因。

通过迭代识别慢查询、测试变体和实施优化的过程，您可以避免猜测，确保每个提示实际上都对您的应用程序有益，并防止给您的数据库增加不必要的复杂性。

结论

将 Oracle 提示迁移到 PostgreSQL 可能是一个棘手的过程，但 pg_hint_plan 在您真正需要指导 Postgres 的规划器时提供了一个有价值的工具。尽管如此，请记住，PostgreSQL 旨在根据强大的统计数据、战略索引和精心选择的成本参数做出合理的决策，所有这些都可以使用 pganalyze 进行优化。提示应该作为有针对性的解决方案，而不是默认方法

#PG证书#PG考试#PostgresQL培训#PostgresQL考试#PostgresQL认证

【PGCCC】在 PostgreSQL 上使用 pg_hint_plan替换 Oracle Hints 的最佳实践