pg_hint_plan: 提示的详细用法

二月 5, 2024

摘要：在本文中，我们来看看pg_hint_plan在各种场景下的详细用法。

目录

语法和位置

pg_hint_plan仅从第一个块注释中读取提示，并停止解析除字母字符、数字、空格、下划线、逗号和括号之外的任何字符。在以下示例中，HashJoin(a b)和SeqScan(a)被解析为提示，但IndexScan(a)和MergeJoin(a b)不是：

=# /*+
     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=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)

在 PL/pgSQL 中使用

pg_hint_plan可用于 PL/pgSQL 脚本中的查询，但会有一些限制。

  • 提示仅影响以下类型的查询：
    • 返回一行的查询（SELECT、INSERT、UPDATE和DELETE）
    • 返回多行的查询（RETURN QUERY）
    • 动态 SQL 语句（EXECUTE）
    • 游标打开（OPEN）
    • 循环遍历一个查询的结果（FOR）
  • 一个提示注释必须放在查询中的第一个单词之后，因为前面的注释不会作为此查询的一部分处理。
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;

对象名称中的大小写处理

与 PostgreSQL 处理对象名称的方式不同，pg_hint_plan以区分大小写的方式，将提示中的对象名称与数据库内部对象名称进行比较。 因此，一个提示中的对象名称 TBL 仅与数据库中的 “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)

视图或规则中的基础表

提示不适用于视图，但如果对象名称与视图上展开的查询中的名称匹配，则提示可以影响视图中的查询。通过为视图中的表分配别名，可以从视图外部操作它们。

=# CREATE VIEW v1 AS SELECT * FROM t2;
=# EXPLAIN /*+ HashJoin(t1 v1) */
          SELECT * FROM t1 JOIN v1 ON (c1.a = v1.a);
                            QUERY PLAN
------------------------------------------------------------------
 Hash Join  (cost=3.27..18181.67 rows=101 width=8)
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on t1  (cost=0.00..14427.01 rows=1000101 width=4)
   ->  Hash  (cost=2.01..2.01 rows=101 width=4)
         ->  Seq Scan on t2  (cost=0.00..2.01 rows=101 width=4)

表继承

提示只能指向继承层级树中的父表，并且提示会影响继承树中的所有表。直接指向继承树中的子表的提示不会起作用。

多语句中的提示

一个多语句只能有一个提示注释，并且该提示会影响多语句中的所有单个语句。

VALUES 表达式

FROM子句中的VALUES表达式在内部被命名为*VALUES*，如果它是一个查询中唯一的VALUES，则可以提示这些表达式。查询中的两个或多个VALUES表达式无法通过查看EXPLAIN结果来区分，从而导致结果不明确：

=# /*+ MergeJoin(*VALUES*_1 *VALUES*) */
   EXPLAIN SELECT * FROM (VALUES (1, 1), (2, 2)) v (a, b)
     JOIN (VALUES (1, 5), (2, 8), (3, 4)) w (a, c) ON v.a = w.a;
INFO:  pg_hint_plan: hint syntax error at or near "MergeJoin(*VALUES*_1 *VALUES*) "
DETAIL:  Relation name "*VALUES*" is ambiguous.
                               QUERY PLAN
-------------------------------------------------------------------------
 Hash Join  (cost=0.05..0.12 rows=2 width=16)
   Hash Cond: ("*VALUES*_1".column1 = "*VALUES*".column1)
   ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.04 rows=3 width=8)
   ->  Hash  (cost=0.03..0.03 rows=2 width=8)
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=8)

子查询

子查询上下文偶尔可以使用名称ANY_subquery进行提示：

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

对于这些语法，规划器在对包含子查询的表进行连接时，会在内部赋予名称给子查询，因此连接提示适用于此类使用隐式名称的连接。例如：

=# /*+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.49..2903.00 rows=1 width=97)
   Hash Cond: (a1.aid = a2.bid)
   ->  Seq Scan on pgbench_accounts a1  (cost=0.00..2640.00 rows=100000 width=97)
   ->  Hash  (cost=0.36..0.36 rows=10 width=4)
         ->  Limit  (cost=0.00..0.26 rows=10 width=4)
               ->  Seq Scan on pgbench_accounts a2  (cost=0.00..2640.00 rows=100000 width=4)

使用 IndexOnlyScan 提示

当 IndexOnlyScan 提示中指定的索引无法执行仅索引扫描时，可能会意外地选择另一个索引执行索引扫描。

关于 NoIndexScan

一个NoIndexScan提示隐含地表示了NoIndexOnlyScan。

并行提示和 UNION

仅当所有基础的子查询都是并行安全的时候，UNION组合查询才能并行运行。因此，强制在任何子查询上并行执行，将使可并行执行的UNION并行运行。同时，零工作进程的并行提示可防止扫描以并行方式执行。

通过提示设置 pg_hint_plan 参数

pg_hint_plan参数会影响其自身的行为，因此某些参数将无法按预期工作：

  • 更改enable_hint和enable_hint_tables的提示会被忽略，即使它们在调试日志中报告为 “已启用提示”。
  • 在查询处理过程中，设置debug_print和message_level。

了解更多

pg_hint_plan: 简介

pg_hint_plan: 提示表

搜索

分类

标签