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

如果您要从 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 替代方案。

访问路径（或索引）提示

在 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中的示例用法，将并行工作者从默认值 2（max_parallel_workers_per_gather）增加到 4，仅用于此查询对“sales”表的使用：

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

查询转换和子查询提示

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

结果缓存和其他专门提示

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

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

1. Rows(table1 table2 [ n ])table1 and table2：告诉规划器假设返回行之间存在连接n（替换或调整统计得出的估计值），影响连接顺序和计划选择。
2. 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认证