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

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

依靠 PostgreSQL 基于成本的规划器

• 表大小、列数据分布等的统计信息。

• 规划器成本设置如random_page_cost和cpu_tuple_cost

• 服务器配置参数，例如enable_seqscan、work_mem和effective_cache_size

Postgres 规划器问题的根本原因

当提示有帮助时

将 Oracle 提示映射到 pg_hint_plan

• 强制使用特定索引或连接方法（例如嵌套循环）

• 启用或禁用并行执行

• 覆盖其他计划选择

这些提示可能非常直接：“在此表上使用索引 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 替代方案。

访问路径（或索引）提示

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;

连接操作提示

加入顺序提示

并行/并行度提示

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

查询转换和子查询提示

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

结果缓存和其他专门提示

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

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

调试 pg_hint_plan 提示的最佳实践

• 指定多个提示注释（如果有多个提示，则必须在一个/*+ ... */注释中全部指定它们）

• 使用不正确的 pg_hint_plan 语法（例如NestedLoop代替NestLoop）

• 规划器没有可行的路径来使用提示（例如，因为请求的索引不能用于给定的表达式）

• 查询中重复使用的表名没有唯一的别名（在这种情况下，您需要为每个表分配一个别名）

• 分区表的提示必须针对分区表父级，而不是子级

• 没有指定名称的子查询（即不是 CTE）只能在某些情况下提示

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 测试查询提示

结论

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