PostgreSQL优化器提示扩展——pg_hint_plan

PostgreSQL使用基于代价的优化器，代价的计算是基于统计信息，而统计信息的准确性和实时性是需要平衡的，精确度十分高的统计信息也会影响性能，统计信息收集与存储这也是需要消耗资源的，这是需要在工程实现上进行平衡，因为统计信息的误差以及优化器代价计算模型是一个通用的模型，它的首要目标是让大多数场景的SQL生成一个最佳执行计划，但是对某些特殊情况，可能生成的是一个次优的执行计划。那么在已知优化器生成的是非最佳执行计划的情况下，怎么去进行优化呢？可通过优化器提示pg_hint_plan来优化，pg_hint_plan会通过特殊的注释语句提示来调整既定的执行计划，去生成你想要的执行计划。

使用场景

当前主流数据库都有优化器提示这个功能，其中Oracle优化器提示功能做的十分强大，那么为什么会有优化器提示这个功能呢？就是为了给开发人员一个机制，当优化器无法生成最佳执行计划时，能够有一种方法可以改变这个执行计划，生成最优执行计划。当前的优化器无法保证所有的SQL生成的执行计划都是最优的。当然，影响执行计划生成的不只是SQL，因为还有数据量，统计信息等都会影响优化器的执行计划生成。

那么什么时候才该用hint呢？当生成的执行计划不符合要求时，我们首先要考虑的是SQL设计的是否合理，比如有没有建索引等，其次看统计信息是否准确，代价模型是否合理，比如硬件不同，相应的代价参数需要进行调整，比如random_page_cost参数磁盘与SSD参数就不同，可通过调整GUC参数来改变。最后才是通过hint的方式来调整执行计划，因为这样的方式只能调整一个执行计划，只针对当前hint的SQL有效，并且随着数据量以及统计信息的变化，原有的hint提示的执行计划可能也并不是最优的执行计划，所以采用hint的方式来进行优化需要开发人员进行具体的权衡。

使用方法

安装

修改postgresql.conf文件，添加如下内容：

shared_preload_libraries = 'pg_hint_plan'
pg_hint_plan.enable_hint_table = on

安装扩展：

postgres=# create extension pg_hint_plan ;
CREATE EXTENSION

当前已支持PostgreSQL 18，如果是PostgreSQL 14，请使用pg_hint_plan 1.4版本。

基础用法

一、语法表示

hint通过解析SQL语句中特定格式的注释来读取优化器提示信息，具体的提示信息需要以/*+开始，以*/结尾， 提示短语由提示名称及括号内的参数构成，参数之间使用空格分隔，为了提高可读性，提示短语允许换行。例如/*+ SeqScan(a) */表示使用SeqScan方法扫描表a。我们举个例子说明，当然这个例子不一定使用Hint会更优，仅说明一下hint的用法以及效果。

-- 未使用hint
postgres=# EXPLAIN SELECT * FROM pgbench_branches b JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;
                           QUERY PLAN                                                    
------------------------------------------------------------------------
 Nested Loop  (cost=0.57..94043.52 rows=1001393 width=461)
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts a  (cost=0.42..70236.02 rows=1001393 width=97)
   ->  Memoize  (cost=0.15..0.16 rows=1 width=364)
         Cache Key: a.bid
         Cache Mode: logical
         ->  Index Scan using pgbench_branches_pkey on pgbench_branches b  (cost=0.14..0.15 rows=1 width=364)
               Index Cond: (bid = a.bid)
(7 rows)

使用hint后，变更了执行计划。

-- 使用hint
postgres=# /*+
     HashJoin(a b)
     SeqScan(a)
    */
   EXPLAIN SELECT * FROM pgbench_branches b JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;
                        QUERY PLAN                                        
-------------------------------------------------------------------------
 Sort  (cost=407325.98..409829.46 rows=1001393 width=461)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.23..62937.86 rows=1001393 width=461)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..59193.93 rows=1001393 width=97)
         ->  Hash  (cost=1.10..1.10 rows=10 width=364)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364)
(7 rows)

pg_hint_plan仅解析首个块注释中的提示信息，遇到非字母、数字、空格、下划线、逗号及括号字符时停止解析。下例中，HashJoin(a b)和SeqScan(a)被识别为提示，而IndexScan(a)与MergeJoin(a b)无效：

postgres=# /*+
     HashJoin(a b)
     SeqScan(a)
    */
   /*+ IndexScan(a) */
   EXPLAIN SELECT /*+ MergeJoin(a b) */ * FROM pgbench_branches b JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;
                       QUERY PLAN                                        
-------------------------------------------------------------------
 Sort  (cost=407325.98..409829.46 rows=1001393 width=461)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.23..62937.86 rows=1001393 width=461)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..59193.93 rows=1001393 width=97)
         ->  Hash  (cost=1.10..1.10 rows=10 width=364)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364)
(7 rows)

我们看一下日志，可以看到仅SeqScan以及HashJoin被使用了，其他两个hint无效。

2025-07-29 14:41:49.178 CST [13375] DEBUG:  pg_hint_plan:
	used hint:
	SeqScan(a)
	HashJoin(a b)
	not used hint:
	duplication hint:
	error hint:

二、在plpgsql中使用

pg_hint_plan可以在plpgsql中使用，但存在以下限制：

• 提示仅对以下查询类型生效：
  • 返回单行的查询（SELECT、INSERT、UPDATE、DELETE）
  • 返回多行的查询（RETURN QUERY）
  • 动态SQL语句（EXECUTE）
  • 打开游标（OPEN）
  • 遍历查询结果（FOR）
• 提示注释必须紧接在查询首个关键字之后，前置注释不会被视为查询的一部分。

postgres=# CREATE FUNCTION hints_func(integer) RETURNS integer AS $$
   DECLARE
     id  integer;
     cnt integer;
   BEGIN
     SELECT /*+ NoIndexScan(a) */ aid
       INTO id FROM pgbench_accounts a WHERE aid = $1;
     SELECT /*+ SeqScan(a) */ count(*)
       INTO cnt FROM pgbench_accounts a;
     RETURN id + cnt;
   END;
   $$ LANGUAGE plpgsql;
CREATE FUNCTION

三、对象名称大小写处理、特殊字符处理

与PostgreSQL不同，pg_hint_plan对提示中的对象名采用严格区分大小写的匹配方式。例如，提示中的TBL仅匹配数据库中名为"TBL"的对象，不会匹配tbl、Tbl等未加引号的变体。

若提示参数中的对象名包含括号、双引号或空格，需使用双引号包裹，转义规则与PostgreSQL一致。

四、区分多处出现的表

当相同的表名多次出现时，pg_hint_plan通过别名识别目标对象。此特性可用于精准指定特定表实例：

=# /*+ HashJoin(t1 t1) */
   EXPLAIN SELECT * FROM s1.t1
     JOIN public.t1 ON (s1.t1.id=public.t1.id);
INFO:  hint syntax error at or near "HashJoin(t1 t1)"
DETAIL:  Relation name "t1" is ambiguous.
...
=# /*+ HashJoin(pt st) */
   EXPLAIN SELECT * FROM s1.t1 st
     JOIN public.t1 pt ON (st.id=pt.id);
                             QUERY PLAN
---------------------------------------------------------------------
 Hash Join  (cost=64.00..1112.00 rows=28800 width=8)
   Hash Cond: (st.id = pt.id)
   ->  Seq Scan on t1 st  (cost=0.00..34.00 rows=2400 width=4)
   ->  Hash  (cost=34.00..34.00 rows=2400 width=4)
         ->  Seq Scan on t1 pt  (cost=0.00..34.00 rows=2400 width=4)

五、视图或规则下的表

hint无法直接作用于视图。 这是因为pg_hint_plan通常是在查询重写阶段之后，执行器之前进行处理，而视图是在查询重写阶段进行处理的，在查询重写阶段视图已经被替换为对应的表，生成的查询树中已经没有视图了。

postgres=# create view vt2 as select * from t2;
CREATE VIEW
postgres=# /*+ SeqScan(vt2) */ explain select * from vt2 where a = 10;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Index Scan using t2_pkey on t2  (cost=0.29..2.50 rows=1 width=17)
   Index Cond: (a = 10)
(2 rows)

postgres=# /*+ SeqScan(t2) */ explain select * from t2 where a = 10;
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on t2  (cost=0.00..180.00 rows=1 width=17)
   Filter: (a = 10)
(2 rows)

六、继承表

提示仅对父表有效，且会影响所有子表。直接针对子表的提示无效。

七、子查询

对于以下类型的子查询，可通过隐含名ANY_subquery来提示：

    IN (SELECT ... {LIMIT | OFFSET ...} ...)
    = ANY (SELECT ... {LIMIT | OFFSET ...} ...)
    = SOME (SELECT ... {LIMIT | OFFSET ...} ...)

例如：

postgres=# /*+HashJoin(a1 ANY_subquery)*/
   EXPLAIN SELECT * FROM pgbench_accounts a1 WHERE aid IN (SELECT bid FROM pgbench_accounts a2 LIMIT 10);
                          QUERY PLAN                                           
---------------------------------------------------------------
 Hash Semi Join  (cost=0.82..61823.51 rows=10 width=97)
   Hash Cond: (a1.aid = a2.bid)
   ->  Seq Scan on pgbench_accounts a1  (cost=0.00..59193.93 rows=1001393 width=97)
   ->  Hash  (cost=0.69..0.69 rows=10 width=4)
         ->  Limit  (cost=0.00..0.59 rows=10 width=4)
               ->  Seq Scan on pgbench_accounts a2  (cost=0.00..59193.93 rows=1001393 width=4)
(6 rows)

八、IndexOnlyScan提示

若指定的索引无法进行仅索引扫描，可能会意外选择其他索引执行索引扫描。

这里补充说明一下，在使用禁止顺序扫描NoSeqScan时，并不意味着不会使用顺序扫描，因为顺序扫描是最基础的扫描方式，当其他扫描不可用时，还是会生成顺序扫描。

-- 表没有索引
postgres=#  \d t4
                 Table "public.t4"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
-- hint提示禁止顺序扫描，但实际上最终还是生成顺序扫描执行计划
postgres=# /*+ NoSeqScan(t4) */ explain select * from t4 where a < 30000;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Seq Scan on t4  (cost=10000000000.00..10000000038.25 rows=753 width=8)
   Filter: (a < 30000)
(2 rows)

九、关于NoIndexScan

NoIndexScan提示隐含启用NoIndexOnlyScan。

十、 并行提示与UNION

UNION只有当所有底层查询都是并行安全的情况下才允许并行。因此，强制对任意可并行安全的子查询进行并行
会让UNION以并行方式执行。

十一、 通过set提示设置pg_hint_plan参数

pg_hint_plan某些参数影响自身行为，因此通过set提示设置的某些参数可能不会像期望的那样工作，比如：

• 修改enable_hint，enable_hint_table的参数会被忽略。
• 在查询执行过程中设置debug_print和message_level参数。

pg_hint_plan配置参数

pg_hint_plan配置参数如下：

错误处理

在开发测试阶段，建议开启日志，并设置日志级别为DEBUG级别，方便观察错误原因。

postgres=# set pg_hint_plan.debug_print to on;
SET
postgres=# set pg_hint_plan.message_level to debug1;
SET

我们举个例子：

-- 引用无效表c
postgres=# /*+
     HashJoin(a b c)   
     SeqScan(a)
    */
   EXPLAIN SELECT *
     FROM pgbench_branches b
     JOIN pgbench_accounts a ON b.bid = a.bid
     ORDER BY a.aid;
                        QUERY PLAN                                        
-------------------------------------------------------------
 Sort  (cost=407325.98..409829.46 rows=1001393 width=461)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.23..62937.86 rows=1001393 width=461)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..59193.93 rows=1001393 width=97)
         ->  Hash  (cost=1.10..1.10 rows=10 width=364)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364)
(7 rows)

日志中会提示没有使用HashJoin(a b c)，若提示中引用的对象（如表、索引等）定义不正确，相关提示会被静默忽略。此类错误在服务器日志中会标记为not used hint。

2025-07-29 11:47:30.100 CST [13375] DEBUG:  pg_hint_plan:
	used hint:
	SeqScan(a)
	not used hint:  
	HashJoin(a b c)
	duplication hint:
	error hint:

pg_hint_plan在遇到任何错误时都会停止解析hint，并仅使用已经解析的hint。以下是一些典型的错误类型：

• 语法错误： 任何语法错误或无效的提示名称都会被视为语法错误。
• 不正确的对象定义： 若提示中引用的对象（如表、索引等）定义不正确，相关提示会被静默忽略。
• 冗余或冲突的提示：当存在冗余提示或相互冲突的提示时，仅最后定义的提示生效。此类错误会记录为“重复提示”（duplicated hints）。
• 嵌套注释：提示注释（/*+ ... */）不能嵌套。一旦检测到嵌套注释，解析会立即终止，所有已解析的提示将被忽略。

使用注意事项

一、查询优化器GUC参数的影响

如果FROM子句中表的数量超过了from_collapse_limit（默认值为8），那么查询优化器将不会尝试优化表连接顺序，此时pg_hint_plan无法影响表连接顺序。

二、强制执行不可行执行计划的提示

当强制执行的计划因条件不满足而无法执行时，查询优化器将会选择任意可执行的计划：

• 尝试对 FULL OUTER JOIN 使用嵌套循环（Nested Loop）。
• 使用未被包含的索引。
• 对不含 ctid 条件的查询尝试执行 TID 扫描。

三、ECPG中的查询

ECPG（嵌入式 SQL）会移除嵌入式SQL语句中的注释，因此提示无法通过常规方式传递。唯一的例外是 EXECUTE 命令，它会将原始查询字符串直接发送到服务器。在此场景下，可以通过hint table来应用提示。

四、查询标识符

启用 compute_query_id 后，PostgreSQL生成查询ID时会忽略注释。因此，即使查询包含不同的提示（但主体结构相同），仍可能生成相同的查询ID。

支持列表

当前主要支持扫描方法、连接方法、连接顺序、连接行为控制、行数校正、并行查询、GUC参数配置等提示。具体支持如下：

如未来有更新版本，可查看文档Hint list。

参考文档：
pg_hint_plan
SQL优化：一篇文章说清楚Oracle Hint的正确使用姿势