pg_hint_plan

pg_hint_plan插件通过特殊的注释语句提示来调整既定的执行计划。

背景信息

PolarDB PostgreSQL版(兼容Oracle)使用基于代价的优化器,优化路线使用统计数据而非固定的规则。对于一条SQL语句,优化器会去评估所有可能的代价并最终选择代价最低的去执行。优化器会尽力选择最好的执行计划,但由于其并不了解数据中可能存在的一些内在连接关系,导致这些执行计划可能并不完美。

通过指定GUC变量,可以调整执行计划,但会影响整个会话。而pg_hint_plan只调整单个执行计划,不影响整个会话,可以优化执行计划。

注意事项

  • DMS暂时不支持提示注释,请使用其他连接方式连接数据库。

  • pg_hint_plan插件只识别第一个注释块中的内容。

  • 当进行扫描时,如果遇到了除字母、数字、空格、下划线、逗号、圆括号以外的字符时会立即停止扫描。

  • pg_hint_plan插件对于对象的处理与PostgreSQL并不一致,只会按照对象的对象名进行比较。例如,一个名为TBL的对象在提示语句中只会匹配TBL,而不会匹配tblTbl

使用限制

在PL/pgSQL存储过程中使用pg_hint_plan插件的限制如下:

  • 提示只对以下类型的语句生效:

    • 返回一行的查询(SELECT、INSERT、UPDATE、DELETE)。

    • 返回多行的查询(RETURN QUERY)。

    • 执行SQL语句(EXECUTE QUERY)。

    • 打开游标(OPEN)。

    • 对查询结果的遍历(FOR)。

  • 一个提示语句必须被放置于每个查询的第一个单词之后,因为过早的提示语句无法被认为是该查询的一部分。

创建插件

  1. 创建插件。

    CREATE EXTENSION pg_hint_plan;
  2. 加载插件。

    • 单个用户自动加载。

      • 执行以下命令,加载插件。

        ALTER USER xxx set session_preload_libraries='pg_hint_plan';
        说明

        其中,xxx需要更改为实际登录的用户名。

      • 执行以下命令,对单独数据库自动加载。

        ALTER DATABASE xxx set session_preload_libraries='pg_hint_plan';
      说明

      如果配置错误导致无法登录数据库,则需要以其它用户/数据库登录到PolarDB中,进行重置:

      ALTER USER xxx reset session_preload_libraries;
      ALTER DATABASE xxx reset session_preload_libraries;
    • 数据库集群自动加载。

      请前往配额中心,在配额名称PolarDB PG pg_hint_plan使用操作列,单击申请,申请添加pg_hint_plan插件。

    • 查看是否已加载插件。

      1. 执行以下命令,将调试信息输出到客户端。

        SET pg_hint_plan.debug_print TO on;
        SET pg_hint_plan.message_level TO notice;
      2. 执行以下命令,查看是否加载成功。

        /*+Set(enable_seqscan 1)*/select 1;

        显示结果如下,表示加载成功。

        NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)
      3. 执行以下命令,关闭调试信息输出。

        RESET pg_hint_plan.debug_print;
        RESET pg_hint_plan.message_level;

使用说明

  • 注释提示

    pg_hint_plan的注释以/*+开头,以*/结束。提示语句包括提示名和参数(参数使用括号包裹,参数之间使用空格分隔)。为了增加可读性,每一个提示语句都可以重新换行。

    示例

    HashJoin作为连接方法,并且使用序列扫描SeqScan来扫描表pgbench_accounts:

    /*+
       HashJoin(a b)
       SeqScan(a)
     */
    EXPLAIN SELECT *
       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)
                            
  • 提示表

    虽然可以使用注释提示的方式对SQL语句进行提示,但是当SQL语句无法编辑时,该提示方式便不可用。对于这种情况,可以将这些提示放在一张特殊的表hint_plan.hints中。表结构如下所示:

    字段

    说明

    id

    提示ID号,唯一且自动填充。

    norm_query_string

    与要提示的查询匹配的模式。所有的常量可以被替换为 ,并且空格在这个模式中是有意义的。

    application_name

    应用会话的名称,置空表示任何应用。

    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

提示类型

  • 提示类型

    根据提示短语影响执行计划的方式,支持的提示类型包括扫描方法提示、连接方法提示、连接顺序提示、行数校正提示、并行执行提示和GUC参数设置提示。

    • 扫描方法提示

      扫描方法提示对目标表强制执行特定的扫描方法,pg_hint_plan通过表的别名(如果存在的话)来识别目标表。扫描方法可能是序列扫描、索引扫描等。

      扫描提示对普通表、继承表、无日志表、临时表和系统表有效。对外部表、表函数、常量值语句、通用表达式、视图和子查询无效。

      示例命令如下:

      /*+
          SeqScan(t1)
          IndexScan(t2 t2_pkey)
       */
       SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
    • 连接方法提示

      连接方法提示强制指定相关表聚合在一起的方法。对普通表、继承表、无日志表、临时表、外部表、系统表、表函数、常量值命令和通用表达式有效。对视图和子查询无效。

    • 连接顺序提示

      连接顺序提示指定两张或多张表的连接顺序。包括两种强制指定方法:

      • 强制执行特定的连接顺序,但不限制每个连接级别的方向。

      • 强制连接方向。

      示例命令如下:

       /*+
          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);
      说明

      其中

      • NestLoop(t1 t2):指定表t1和t2的连接方法。

      • MergeJoin(t1 t2 t3):指定表t1、t2和t3之间的连接方法。

      • Leading(t1 t2 t3):指定三张表的连接顺序。

    • 行数校正提示

      行数校正提示会校正由于查询优化器限制而导致的行数错误。

       /*+ Rows(a b #10) */ SELECT... ; # 设置连接结果的行数为10
       /*+ Rows(a b +10) */ SELECT... ; # 行数增加10
       /*+ Rows(a b -10) */ SELECT... ; # 行数减去10
       /*+ Rows(a b *10) */ SELECT... ; # 行数增大10倍
    • 并行执行提示

      并行执行提示会指定并行的执行计划。

      并行级别提示对普通的表、继承表、无日志表和系统表有效。对外部表、常量从句、通用表达式、视图和子查询无效。视图的内部表可以通过其真实名称或别名指定目标对象。

      下面两个示例说明在每张表上执行查询的方式不同:

      • 方式一:指定表c1的并行度为3,表c2的并行度为5。

        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)
                                                
      • 方式二:指定表t1的并行度为5。

        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参数进行多次设置,则以最后一个为准。

      示例命令如下:

       /*+ Set(random_page_cost 2.0) */
       SELECT * FROM table1 t1 WHERE key = 'value';
  • 提示格式列表

    所有提示支持的格式如下所示。在注释中增加以下格式的字段,可以使用相应的功能。其中[]表示可选参数。

    类型

    格式

    说明

    扫描方法提示(Scan method)

    SeqScan(table)

    强制对名为table的表使用Sequence Scan。

    TidScan(table)

    强制对名为table的表使用TID Scan。

    IndexScan(table[ index...])

    强制对名为table的表使用Index Scan,如果在后面增加index名称,则可以指定需要使用的索引。

    IndexOnlyScan(table[ index...])

    强制对名为table的表使用Index Only Scan,如果在后面增加index名称,则可以指定需要使用的索引。

    BitmapScan(table[ index...])

    强制对名为table的表使用Bitmap Index Scan,如果在后面增加index名称,则可以指定需要使用的索引。

    NoSeqScan(table)

    禁止对名为table的表使用Seqence Scan。

    NoTidScan(table)

    禁止对名为table的表使用Tid Scan。

    NoIndexScan(table)

    禁止对名为table的表使用Index Scan。

    NoIndexOnlyScan(table)

    禁止对名为table的表使用Index Only Scan。

    NoBitmapScan(table)

    禁止对名为table的表强制使用Bitmap Index Scan。

    连接方法提示(Join method)

    NestLoop(table table[ table...])

    对包含指定表名的表之间的Join连接操作,强制使用Nest Loop Join进行连接操作。

    HashJoin(table table[ table...])

    对包含指定表名的表之间的Join连接操作,强制使用Hash Join进行连接操作。

    MergeJoin(table table[ table...])

    对包含指定表名的表之间的Join连接操作,强制使用Merge Join进行连接操作。

    NoNestLoop(table table[ table...])

    对包含指定表名的表之间的Join连接操作,禁止使用Nest Loop Join进行连接操作。

    NoHashJoin(table table[ table...])

    对包含指定表名的表之间的Join连接操作,禁止使用Hash Join进行连接操作。

    NoMergeJoin(table table[ table...])

    对包含指定表名的表之间的Join连接操作,禁止使用Merge Join进行连接操作。

    连接顺序提示(Join order)

    Leading(table table[ table...])

    指定表之间进行Join连接的顺序。

    Leading(<join pair>)

    指定两个表之间进行Join的先后顺序。

    行数校正提示(Row number correction)

    Rows(table table[ table...] correction)

    校正由指定表组成的联结结果的行数。 可用的校正方法包括绝对值(#<n>)、加法(+ <n>)、减法(-<n>)和乘法(* <n>), 其中<n>表示需要指定的行的数量。

    并行执行提示(Parallel query configuration)

    Parallel(table <# of workers> [soft|hard])

    强制或禁止并行执行针对指定表的扫描。

    说明
    • <# of workers>是所需的并行度(并行执行的程序数量),其中0表示禁止并行执行。

    • 如果第三个参数是soft(默认),表示仅修改max_parallel_workers_per_gather参数的值,由优化器决定实际的并行度。

    • hard表示强制使用其指定的并行度。

    PX(<# of workers>)

    表示跨机并行查询时进行并行查询。

    说明

    <# of workers>表示并行度。

    NoPX()

    表示针对此查询强制不使用跨机并行查询功能。

    GUC参数配置提示

    Set(GUC-param value)

    优化器运行时,将GUC参数设置为该值。

    说明

    pg_hint_plan也可以指定跨机并行查询生成的查询计划。目前在跨机并行查询场景下,不支持行数校正提示,连接方法提示仅能作用于两表之间的连接,连接顺序提示仅能指定全部表之间的先后顺序。