pg_hint_plan

pg_hint_plan通过特殊形式的注释中的提示短语来控制执行计划。

概要

PostgreSQL使用基于代价的优化器，该优化器的代价计算利用的是数据统计信息，而不是静态规则。对于一条SQL语句，优化器会估计所有可能的执行计划的代价，然后最终选择代价最低的执行计划。由于优化器不会考虑列之间的相关性，因此，最终选出来的执行计划可能并不是完美的。

pg_hint_plan允许我们在sql语句中通过特殊格式的提示来调整执行计划，达到优化执行计划的目的。

使用方法

基本使用

pg_hint_plan在目标SQL语句中读取特殊格式的提示短语。提示短语以字符/*+开始，*/结尾。提示短语由提示名和参数（用括号包起来，两个参数之间用空格分隔）组成。为了增加可读性，每一个提示短语可另起一行。

在下面的示例中，HashJoin被选择为连接方法，并使用Seq Scan对表pgbench_accounts进行扫描。

postgres=# /*+
postgres*#    HashJoin(a b)
postgres*#    SeqScan(a)
postgres*#  */
postgres-# EXPLAIN SELECT *
postgres-#    FROM pgbench_branches b
postgres-#    JOIN pgbench_accounts a ON b.bid = a.bid
postgres-#   ORDER BY a.aid;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=197)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

提示表

在上一节中，介绍了pg_hint_plan中提示短语的使用，但是当查询语句无法编辑时，是很不方便的。这种情况，可以将提示放在特殊的表hint_plan.hints中，表结构如下所示：

如何使用表hint_plan.hints

postgres=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
postgres-#     VALUES (
postgres(#         'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
postgres(#         '',
postgres(#         'SeqScan(t1)'
postgres(#     );
INSERT 0 1
postgres=# UPDATE hint_plan.hints
postgres-#    SET hints = 'IndexScan(t1)'
postgres-#  WHERE id = 1;
UPDATE 1
postgres=# DELETE FROM hint_plan.hints
postgres-#  WHERE id = 1;
DELETE 1

用户在创建pg_hint_plan插件时，默认拥有表hint_plan.hints的权限，且提示表中的优先级高于SQL语句中提示短语的优先级。

提示类型

根据对象类型以及如何影响计划提示短语可分为六类。扫描方法、连接方法、连接顺序、行号校正、并行查询和GUC设置。您可以在提示列表中看到每种类型的提示短语列表。

扫描方法提示

扫描方法提示对目标表强制执行特定的扫描方法。pg_hint_plan通过表的别名（如果存在别名）识别目标表。扫描方法可以是，SeqScan、IndexScan等。

扫描提示对普通表、继承表、UNLOGGED表、临时表和系统表都有效。对于外部表、表函数、VALUES子句、通用表表达式（CTEs）、视图和子查询扫描提示无效。

postgres=# /*+
postgres*#     SeqScan(t1)
postgres*#     IndexScan(t2 t2_pkey)
postgres*#  */
postgres-# SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);

连接方法提示

连接方法提示对涉及表的连接方法进行强制指定。

连接方法提示对普通表、继承表、UNLOGGED表、临时表、外部表、系统表、表函数、VALUES子句、通用表表达式（CTEs）有效。对视图和子查询无效。

连接顺序提示

此提示“ Leading”在两个或多个表上强制执行连接顺序。有两种执行方法。一种是强制执行特定的连接顺序，但不限制每个连接级别的方向。另一个加入连接方向。详细信息请参见提示列表。

postgres=# /*+
postgres*#     NestLoop(t1 t2)
postgres*#     MergeJoin(t1 t2 t3)
postgres*#     Leading(t1 t2 t3)
postgres*#  */
postgres-# SELECT * FROM table1 t1
postgres-#     JOIN table table2 t2 ON (t1.key = t2.key)
postgres-#     JOIN table table3 t3 ON (t2.key = t3.key);

行号校正提示

行号纠正提示会纠正由于计划器限制而导致连接的行号错误估计。

postgres=# /*+ Rows(a b #10) */ SELECT... ; --设置连接结果的行号为10
postgres=# /*+ Rows(a b +10) */ SELECT... ; --将行号增加10
postgres=# /*+ Rows(a b -10) */ SELECT... ; --将行号减10
postgres=# /*+ Rows(a b *10) */ SELECT... ; --将行号扩大为原来的10倍

并行查询提示

并行查询提示在扫描时会强制并行执行配置。第三个参数表示强度。soft表示pg_hint_plan只更改max_parallel_worker_per_gather。hard更改所有参数，使提示数字有效。并行查询提示对普通表、继承表、UNLOGGED表和系统表有效；对外部表、表函数、VALUSES子句、通用表表达式（CTEs）、视图和子查询无效。可以通过指定内部表的真实名或别名来作为目标对象。

下面的示例表示每个表执行查询的方式不同。

postgres=# explain /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
       SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Hash Join  (cost=2.86..11406.38 rows=101 width=4)
   Hash Cond: (c1.a = c2.a)
   ->  Gather  (cost=0.00..7652.13 rows=1000101 width=4)
         Workers Planned: 3
         ->  Parallel Seq Scan on c1  (cost=0.00..7652.13 rows=322613 width=4)
   ->  Hash  (cost=1.59..1.59 rows=101 width=4)
         ->  Gather  (cost=0.00..1.59 rows=101 width=4)
               Workers Planned: 5
               ->  Parallel Seq Scan on c2  (cost=0.00..1.59 rows=59 width=4)

postgres=# EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;
                                    QUERY PLAN                                  
-----------------------------------------------------------------------------------
 Finalize Aggregate  (cost=693.02..693.03 rows=1 width=8)
   ->  Gather  (cost=693.00..693.01 rows=5 width=8)
         Workers Planned: 5
         ->  Partial Aggregate  (cost=693.00..693.01 rows=1 width=8)
               ->  Parallel Seq Scan on tl  (cost=0.00..643.00 rows=20000 width=4)

设置临时的GUC参数

在计划时更改GUC参数，如果计划器的配置参数没有与其它任何提示冲突，所设置的GUC就有效。同样的GUC参数，以最后一次设置为准。

postgres=# /*+ Set(random_page_cost 2.0) */
postgres-# SELECT * FROM table1 t1 WHERE key = 'value';

提示列表