pg_hint_plan: 提示表

二月 5, 2024

摘要：pg_hint_plan插件提供了一个提示表，通过该表可以方便地更改查询的执行计划。

介绍

虽然可以在注释中指定提示，但是在无法编辑查询的情况下，这可能会很不方便。在这种情况下，可以将提示放在一个名为“hint_plan.hints”的特殊表中。该表由以下列组成：

列	描述
`id`	用于标识一个提示行的唯一编号。此列按顺序自动填充。
`norm_query_string`	与要提示的查询匹配的模式。查询中的常量会替换为“?”，如下面示例所示。
`application_name`	`application_name`的值，用于限定提示生效的来源会话。以下示例中的提示，会应用于从 psql 连接过来的会话。一个空字符串表示所有会话都会应用该提示。
`hints`	提示短语。这必须是一系列提示，不包括前后的注释标记。

以下示例展示了如何使用提示表进行操作。

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

提示表归pg_hint_plan插件所有者所有，并且具有在用CREATE EXTENSION创建插件时相同的默认权限。提示表中的提示会优先于查询注释中的提示。

提示的类型

提示短语根据对象类型，以及它们如何影响规划器，分为多种类型。有关更多详细信息，请参阅提示列表。

Scan 方法的提示

扫描方法提示用于在目标表上强制执行特定的扫描方法。如果查询中有表别名，pg_hint_plan可以通过别名识别目标表。常见的扫描方法有SeqScan、IndexScan。

扫描提示可用于普通表、继承表、UNLOGGED 表、临时表和系统表。外部表、函数结果表、VALUES 子句、CTE 表达式、视图和子查询不受影响。

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

Join 方法的提示

连接方法提示用于在连接指定的表时强制执行特定的连接方法。

这只会影响普通表上的连接。允许继承表、UNLOGGED 表、临时表、外部表、系统表、函数结果表、VALUES 子句和 CTE 表达式位于参数列表中。视图和子查询的连接不受影响。

Join 顺序的提示

该提示名为 “Leading”，用于在两个或多个表上强制设定连接顺序。有两种方法可以强制设定它。第一种方法强制设定特定的连接顺序，但不限制每个连接层级的连接方向。第二种方法额外强制设定了连接方向。有关更多详细信息，请参阅提示列表。例如：

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

行数更正提示

该提示名为 “Rows”，用于更改来自规划器中限定条件的连接的行数估计。例如：

=# /*+ Rows(a b #10) */ SELECT... ; Sets rows of join result to 10
=# /*+ Rows(a b +10) */ SELECT... ; Increments row number by 10
=# /*+ Rows(a b -10) */ SELECT... ; Subtracts 10 from the row number.
=# /*+ Rows(a b *10) */ SELECT... ; Makes the number 10 times larger.

并行计划的提示

该提示名为Parallel，在扫描时强制设定并行执行的配置。第三个参数指定强制应用的强度。soft意味着只更改max_parallel_worker_per_gather，并将所有其他配置留给规划器进行设置。hard意味着更改其他规划器参数，以便强制应用更新。这可以影响普通表、继承性父表、UNLOGGED 表和系统表。外部表、函数结果表、VALUES子句、CTE 表达式、视图和子查询不受影响。视图内部引用的表可以通过其真实名称或别名指定为目标对象。以下示例显示，对每个表的查询采用不同的并行执行方式：

=# 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)

=# 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 参数

Set提示用于在规划时更改 GUC 参数。查询规划中显示的 GUC 参数应该会对计划产生预期效果，除非有其他提示与规划器方法配置参数冲突。当多个提示更改同一个 GUC 参数时，最后一个提示会生效。pg_hint_plan 的 GUC 参数也可以通过此提示进行设置，但它可能会无法按预期工作。有关详细信息，请参阅功能限制。

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

pg_hint_plan 的 GUC 参数

以下 GUC 参数可用于影响pg_hint_plan的行为：

参数名称	描述	默认值
`pg_hint_plan.enable_hint`	设置为 True 启用`pg_hint_plan`。	`on`
`pg_hint_plan.enable_hint_table`	设置为 True 启用提示表。	`off`
`pg_hint_plan.parse_messages`	指定提示解析错误的日志级别。有效值为`error`、`warning`、`notice`、`info`、`log`、`debug`。	`INFO`
`pg_hint_plan.debug_print`	控制调试输出和详细程度。有效参数为`off`、`on`、`detailed`和`verbose`。	`off`
`pg_hint_plan.message_level`	指定调试输出的消息级别。有效值为`error`、`warning`、`notice`、`info`、`log`、`debug`。	`INFO`