【译】postgresql 扩展工具 hint扩展pg_hint_plan

hint扩展pg_hint_plan

10.1 概要

pg_hint_plan 使得在 SQL 注释中使用所谓的“hints”来调整 PostgreSQL 执行计划成为可能，例如 /*+ SeqScan(a) */。

PostgreSQL 使用基于成本的优化器，它利用数据统计，而不是静态规则。计划器（优化器）估计 SQL 语句的每个可能执行计划的成本，然后最终执行成本最低的执行计划。计划器尽最大努力选择最佳的最佳执行计划，但并不总是完美的，因为它不计算数据的某些属性，例如列之间的相关性。

10.2 描述

基本用法

pg_hint_plan 读取与目标 SQL 语句一起给出的特殊形式的注释中的提示短语。特殊形式以字符序列“/+”开始，以“/”结束。提示短语由提示名称和后面用括号括起来并用空格分隔的参数组成。为了便于阅读，每个提示短语都可以用新行分隔。

在下面的示例中，选择 hash join 作为 joning 方法并通过顺序扫描方法扫描 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)

postgres=# 
复制

10.3 提示表

提示在上述部分的特殊形式的注释中进行了描述。这在无法编辑查询的情况下很不方便。在这种情况下，提示可以放在一个名为“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
postgres=#
复制

提示表由创建者用户拥有，并在创建时具有默认权限。在创建扩展期间。表格提示优先于评论命中

提示的类型

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

1 扫描方法的提示

扫描方法提示对目标表强制执行特定的扫描方法。 pg_hint_plan 通过别名（如果有）识别目标表。在这种提示中它们是 SeqScan、IndexScan 等。

扫描提示对普通表、继承表、UNLOGGED 表、临时表和系统目录有效。外部（外部）表、表函数、VALUES 子句、CTE、视图和子查询不受影响。

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

2 连接方法的提示 Hints for join methods

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

这只会影响普通表、继承表、UNLOGGED 表、临时表、外部（外部）表、系统目录、表函数、VALUES 命令结果和允许在参数列表中的 CTE 的连接。但是视图和子查询的连接不受影响。

3 连接顺序的提示 Hint for joining order

这个提示“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);
复制

4 **行数更正提示 ** Hint for row number correction

由于计划者能力的限制，它在某些条件下错误地估计了结果的数量。这种类型的提示可以纠正它。

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

5 并行计划的提示 Hint for parallel plan

此提示 Parallel 对扫描强制执行并行执行配置。第三个参数指定强制执行的强度。 soft 意味着 pg_hint_plan 只改变 max_parallel_worker_per_gather 并将所有其他的留给规划者。硬更改其他规划器参数以强制应用该数字。这会影响普通表、继承父级、未记录的表和系统目录。外部表、表函数、值子句、CTE、视图和子查询不受影响。视图的内部表可以通过其真实名称/别名指定为目标对象。以下示例显示在每个表上执行的查询不同。

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)
复制

6 GUC参数临时设置

“Set”提示会在计划时更改 GUC 参数。查询计划中显示的 GUC 参数可以对计划产生预期的影响，除非任何其他提示与计划程序方法配置参数冲突。相同 GUC 参数的提示中的最后一个生效。 pg_hint_plan 的 GUC 参数也可以通过这个提示来设置，但它不会像你期望的那样工作。有关详细信息，请参阅限制。

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

pg_hint_plan 的 GUC 参数

下面的 GUC 参数会影响 pg_hint_plan 的行为。