pg_hint_plan 通过特殊形式的注释中的提示短语来控制执行计划，使 PostgreSQL 改变其既定的执行计划。
pg_hint_plan 同调整 GUC 变量来影响执行计划不同，GUC 变量的调整会影响当前会话所有 SQL， 而 pg_hint_plan 只影响指定的单个 SQL 执行计划，达到优化特定 SQL 执行计划的目的。
使用该插件前，需要将 pg_stat_statements 与 pg_hint_plan 加入到 shared_preload_libraries 参数中。关于如何修改参数，请参见修改参数。
创建插件
CREATE EXTENSION pg_hint_plan;
删除插件
DROP EXTENSION pg_hint_plan;
pg_hint_plan 在目标 SQL 语句中读取特殊格式的提示短语。提示短语以字符 /*+ 开始，*/ 结尾。提示短语由提示名和参数组成，使用用括号包起来，两个参数之间用空格分隔。为了增加可读性，每一个提示短语占一行。
以下示例中默认 plan 为：
postgres=# create table a(id int, name text); CREATE TABLE postgres=# create table b(id int, name text); CREATE TABLE postgres=# explain select * from a join b on a.id=b.id; QUERY PLAN ------------------------------------------------------------------ Merge Join (cost=176.34..303.67 rows=8064 width=72) Merge Cond: (a.id = b.id) -> Sort (cost=88.17..91.35 rows=1270 width=36) Sort Key: a.id -> Seq Scan on a (cost=0.00..22.70 rows=1270 width=36) -> Sort (cost=88.17..91.35 rows=1270 width=36) Sort Key: b.id -> Seq Scan on b (cost=0.00..22.70 rows=1270 width=36) (8 rows)
通过 /*+ 开始，*/ 结尾指定 HashJoin 被选择为连接方法 plan 为：
postgres=# /*+ postgres*# HashJoin(a b) postgres*# */ postgres-# explain select * from a join b on a.id=b.id; QUERY PLAN ------------------------------------------------------------------ Hash Join (cost=38.58..346.71 rows=8064 width=72) Hash Cond: (a.id = b.id) -> Seq Scan on a (cost=0.00..22.70 rows=1270 width=36) -> Hash (cost=22.70..22.70 rows=1270 width=36) -> Seq Scan on b (cost=0.00..22.70 rows=1270 width=36) (5 rows)
当查询语句无法编辑时，使用提示短语会遇到很多不便。此时，可以将提示放在特殊的表 hint_plan.hints 中，表结构如下所示：
| 列名 | 描述 |
|---|---|
| id | 提示行唯一标识符。该列按顺序自动填充。 |
| norm_query_string | 与要提示的查询相匹配的模式。查询中的常量必须替换为 ?。空格也需要特别注意。 |
| application_name | 应用该提示的会话名称。空字符串表示任何 application_name 的会话。 |
| hints | 提示短语。不包括注释标记。 |
示例
--hint_plan.hints 表中定义了执行 EXPLAIN (COSTS false) SELECT * FROM a WHERE a.id = xxx 时使用 SeqScan(a) 的提示 postgres=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints) VALUES('EXPLAIN (COSTS false) SELECT * FROM a WHERE a.id = ?;', '', 'SeqScan(a)'); INSERT 0 1 postgres=# update hint_plan.hints set hints='IndexScan(a)' where id=1; UPDATE 1
根据提示短语影响执行计划的方式，可以分为如下六类：扫描方法、连接方法、连接顺序、行号校正、并行查询和GUC设置。可以在提示列表中查看每种类型的提示短语列表。
扫描方法提示对目标表强制执行特定的扫描方法。pg_hint_plan 通过表的别名（如果存在的话）来识别目标表。扫描方法是SeqScan、IndexScan等。
扫描提示对普通表、继承表、UNLOGGED 表、临时表和系统表有效，对外部表、表函数、VALUES 子句、通用表达式、视图和子查询无效。
示例命令如下：
postgres=# /*+ postgres*# IndexScan(a a_pk) postgres*# SeqScan(b) postgres*# */ postgres-# explain select * from a join b on a.id=b.id; QUERY PLAN ----------------------------------------------------------------------------- Hash Join (cost=14.89..48.70 rows=635 width=45) Hash Cond: (b.id = a.id) -> Seq Scan on b (cost=0.00..22.70 rows=1270 width=36) -> Hash (cost=13.64..13.64 rows=100 width=9) -> Index Scan using a_pk on a (cost=0.14..13.64 rows=100 width=9) (5 rows)
连接方法提示强制指定相关表格聚合在一起的方法。
连接方法提示对普通表 、继承表、UNLOGGED 表、临时表、外部表、系统表、表函数、VALUES 子句和通用表达式有效，对视图和子查询无效。
postgres=# /*+ postgres*# MergeJoin(a b) postgres*# IndexScan(a a_pk) postgres*# SeqScan(b) postgres*# */ postgres-# explain select * from a join b on a.id=b.id; QUERY PLAN ----------------------------------------------------------------------- Merge Join (cost=88.31..114.76 rows=635 width=45) Merge Cond: (a.id = b.id) -> Index Scan using a_pk on a (cost=0.14..13.64 rows=100 width=9) -> Sort (cost=88.17..91.35 rows=1270 width=36) Sort Key: b.id -> Seq Scan on b (cost=0.00..22.70 rows=1270 width=36) (6 rows)
此提示“ Leading”在两个或多个表的连接顺序。这里有两种强制指定方法：
强制执行特定的连接顺序，但不限制每个连接级别的方向。
强制连接方向。
示例命令如下：
postgres=# /*+ postgres*# NestLoop(a b) postgres*# MergeJoin(a b c) postgres*# Leading(a b c) postgres*# */ postgres-# EXPLAIN SELECT * FROM a postgres-# JOIN b ON (a.id = b.id) postgres-# JOIN c ON (b.id = c.id); QUERY PLAN -------------------------------------------------------------------------------- Merge Join (cost=368.33..431.99 rows=4032 width=81) Merge Cond: (a.id = c.id) -> Sort (cost=280.16..281.75 rows=635 width=45) Sort Key: a.id -> Nested Loop (cost=0.14..250.60 rows=635 width=45) -> Seq Scan on b (cost=0.00..22.70 rows=1270 width=36) -> Index Scan using a_pk on a (cost=0.14..0.17 rows=1 width=9) Index Cond: (id = b.id) -> Sort (cost=88.17..91.35 rows=1270 width=36) Sort Key: c.id -> Seq Scan on c (cost=0.00..22.70 rows=1270 width=36) (11 rows)
行号纠正提示会纠正由于计划器限制而导致的行号错误。
示例命令如下：
/*+ Rows(a b #10) */ SELECT... ; //设置连接结果的行数量为 10。 /*+ Rows(a b +10) */ SELECT... ; //行数量增加 10。 /*+ Rows(a b -10) */ SELECT... ; //行数量减去 10。 /*+ Rows(a b *10) */ SELECT... ; //将行数量扩大至原来的 10 倍。
并行查询提示在扫描时会强制并行执行配置。
第三个参数表示强度。soft 表示 pg_hint_plan 只更改 max_parallel_worker_per_gather；hard 更改所有参数，使提示数字有效。
并行查询提示对普通表、继承表、UNLOGGED 表和系统表有效；对外部表、表函数、VALUSES 子句、通用表表达式（CTEs）、视图和子查询无效。可以通过指定内部表的真实名或别名来作为目标对象。
下面两个示例说明在每个表上执行查询的方式不同：
postgres=# explain /*+ Parallel(a 2 hard) Parallel(b 3 hard) */ postgres-# select * from a join b on a.id=b.id; QUERY PLAN ----------------------------------------------------------------------------- Hash Join (cost=1.25..12.36 rows=635 width=45) Hash Cond: (b.id = a.id) -> Gather (cost=0.00..0.00 rows=1270 width=36) Workers Planned: 3 -> Parallel Seq Scan on b (cost=0.00..0.00 rows=410 width=36) -> Hash (cost=0.00..0.00 rows=100 width=9) -> Gather (cost=0.00..0.00 rows=100 width=9) Workers Planned: 2 -> Parallel Seq Scan on a (cost=0.00..0.00 rows=59 width=9) (9 rows) postgres=# EXPLAIN /*+ Parallel(a 5 hard) */ SELECT sum(id) FROM a; QUERY PLAN ---------------------------------------------------------------------------------- Finalize Aggregate (cost=3703.27..3703.28 rows=1 width=8) -> Gather (cost=3702.75..3703.26 rows=5 width=8) Workers Planned: 5 -> Partial Aggregate (cost=2702.75..2702.76 rows=1 width=8) -> Parallel Seq Scan on a (cost=0.00..0.00 rows=1081100 width=4) (5 rows)
在计划的时候临时改变 GUC 参数。如果计划器的配置参数没有与其他任何提示冲突，所设置的 GUC 就有效。同样的 GUC 参数设置以最后一次为准。
示例命令如下：
postgres=# /*+ Set(random_page_cost 1.2) */ SELECT * FROM a ta WHERE name = '10abc'; id | name ----+------- 10 | 10abc (1 row)
所有提示支持的格式如下表:
| 类别 | 格式 | 说明 |
|---|---|---|
| 扫描方法提示 | SeqScan(table) | 强制序列扫描。 |
| TidScan(table) | 强制 TID 扫描。 | |
| IndexScan(table[ index...]) | 强制索引扫描，可以指定某个索引。 | |
| IndexOnlyScan(table[ index...]) | 强制仅使用索引扫描，可以指定某个索引。 | |
| BitmapScan(table[ index...]) | 强制使用 Bitmap 扫描。 | |
| NoSeqScan(table) | 强制不使用序列扫描。 | |
| NoTidScan(table) | 强制不使用 TID 扫描。 | |
| NoIndexScan(table) | 强制不使用索引扫描。 | |
| NoIndexOnlyScan(table) | 强制不使用索引扫描，仅扫描表。 | |
| NoBitmapScan(table) | 强制不使用 Bitmap 扫描。 | |
| 连接方法提示 | NestLoop(table table[ table...]) | 强制使用嵌套循环连接。 |
| HashJoin(table table[ table...]) | 强制使用散列连接。 | |
| MergeJoin(table table[ table...]) | 强势使用合并连接。 | |
| NoNestLoop(table table[ table...]) | 强制不使用嵌套循环连接。 | |
| NoHashJoin(table table[ table...]) | 强制不使用散列连接。 | |
| NoMergeJoin(table table[ table...]) | 强制不使用合并连接。 | |
| 连接顺序提示 | Leading(table table[ table...]) | 强制连接的顺序。 |
| Leading( | 强制连接的顺序和方向。 | |
| 行号纠正提示 | Rows(table table[ table...] correction) | 纠正由指定表组成的联接结果的行号。可用的校正方法为绝对值 #<n>、加法 +<n>、减法 -<n>和乘法 *<n>。<n> 是 strtod 函数可以读取的数字。 |
| 并行执行提示 | Parallel(table <# of workers> [soft|hard]) | 强制或禁止并行执行指定表。 <worker#> 是所需的并行工作程序数量，0 表示禁止并行执行。第三个参数如果是 soft（默认），表示仅更改 max_parallel_workers_per_gather 并将其他所有内容留给计划器选择；如果是 hard，表示所有相关参数都会被强制指定。 |
| 设置临时 GUC 参数 | Set(GUC-param value) | 规划器运行时，将 GUC 参数设置为该值。 |
更多信息，请参见 PostgreSQL 官方文档。