Hint作为一种SQL补充语法,允许用户通过相应的语法改变SQL的执行方式,实现SQL的特殊优化。Hologres从V2.2版本开始,提供Hint语法,以便您使用Hint改变SQL的执行方式,辅助业务进行SQL调优,实现更好的性能。本文为您介绍Hint的用法以及使用场景。

使用限制

Hint功能要求Hologres实例版本必须为V2.2及以上版本,若您的实例为V2.1或以下版本,请升级实例。

使用说明

  • 目前支持对常规表(包括外部表)、子查询、CTE(Common Table Expression)或视图指定Hint。

  • Hint的所有内容均包含在/*+HINT*/之间,Hint中不允许再出现注释。

  • Hint关键词不区分大小写。

  • 一段Hint内容中可以同时包含多个Hint关键词。

  • Hint有对应的层级,指定的参数为当前作用域内的可见参数,不可使用子查询或父查询中的参数,如下述SQL中的Hint/*+HINT Leading(tt t2) */只能指定ttt2为参数,无法使用t1、t3、t。同理,/*+HINT Leading(t t1) */的位置只能使用tt1,无法使用t2、t3、tt

    SELECT /*+HINT Leading(t t1) */ * FROM t1 join (
      SELECT /*+HINT Leading(tt t2) */ * FROM t2 join (
        SELECT * FROM t3
      ) tt
    ) t;
  • 对于INSERT INTO ... SELECT语句,INSERT的作用域包含目标表和后面SELECT最外层的源表,SELECT的作用域不包含目标表。且为了避免冲突情况,当在INSERT后指定了对应的Hint时,SELECT中不能再次指定Hint。示例如下:

    • 正确示例

      --示例1:INSERT后的Hint支持指定target、t1、t2为参数。
      INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a
      
      --示例2:SELECT后的Hint只支持指定t1、t2为参数。
      INSERT INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;
    • 错误示例

      --不能在INSERT和SELECT后同时使用Hint,否则执行会报错。
      INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;
      
      --报错信息
      ERROR: insert statement with hint should not have sub select with hint at the same time
  • GUC Hint仅对当前Query级别生效,且在任意层级下设置都会影响整个Query。当前Query执行完成后,后续Query将不会受GUC Hint的影响。

    示例:在子查询中设置GUC关闭count distinct reuse会对整个Query都生效。

    SELECT
        count(DISTINCT a),
        count(DISTINCT b)
    FROM (
        SELECT
            /*+HINT set(hg_experimental_enable_reuse_cte_of_count_distinct off) */
            t1.a t2.b
        FROM
            t1
            JOIN t2 ON t1.a = t2.a)
  • Hint允许在参数中使用括号以提高优先级,允许任意的括号嵌套。例如Leading(t1 t2 t3)表示t1和t2先进行Join,再Join t3,而Leading(t1 (t2 t3))表示先执行t2 Join t3,再和t1进行Join。

  • Join Method Hint和Join Order Hint均要求至少两个以上的有效参数,参数不足时,Hint不会生效。

    说明

    有效参数:指当前层级作用域中包含的表、子查询、CTE或视图,如Leading(t1 t1)Leading(t1)中都只有t1一个有效参数。

  • Runtime Filter Hint只能对HashJoin生效。

  • 当生成的候选计划不包含Join Method指定的表连接方式时,对应的Hint不会生效。例如指定HashJoin(t1 t2),但生成的计划为t1 Join t3后再Join t2,此时Hint不会生效,可以通过添加Leading(t1 t2)指定Join Order来强制执行连接顺序。

  • SELECT后连续多段由/*HINT+*/括起的内容,只有第一段会被作为Hint处理。例如SELECT /*+HINT HashJoin(t1 t2) */ /*+HINT Leading(t1 t2) */ ...语句中,只有HashJoin会被处理,而Leading的内容会被忽略。

  • 同一类Hint中定义的表存在冲突时,以先定义的Hint为准。

    说明

    冲突包含如下几种场景:

    • 两个Hint中包含相同的表。

    • 表集合相同。

    • Join Order时,Hint参数互为子集。

    • Join Method、Runtime Filter或skew Join时,Hint参数不互为子集。

    • 示例1:HashJoin(t1 t2)NestLoop(t2 t1)中包含相同的表,产生冲突,只解析HashJoin(t1 t2)

      SELECT /*+HINT HashJoin(t1 t2) NestLoop(t2 t1) */ ...
    • 示例2:Leading(t1 t2)Leading(t1 t2 t3)互为子集,产生冲突,因此只解析Leading(t1 t2)

      SELECT /*+HINT Leading(t1 t2) Leading(t1 t2 t3) */ ...
  • 当指定的Hint计划不满足生成条件时,会导致无法生成执行计划。例如指定两表进行NestLoop和Right Join时,由于不支持这样的计划,会报错ERROR: ORCA failed to produce a plan : No plan has been computed for required properties,即属性不满足无法生成计划。

Hint关键词

目前各类型支持的Hint关键词及对应的参数格式如下。

类型

参数格式

描述

示例

注意事项

Join Method

NestLoop(table1 table2[ table...])

强制使用嵌套循环连接。

SELECT /*+HINT NestLoop(table1 table2) */ * FROM table1 JOIN table2 ON table1.a = table2.a;

至少包含两个有效参数才能触发Hint。

说明

有效参数指当前层级作用域中包含的表、子查询、CTE(Common Table Expression)或视图。

HashJoin(table1 table2[ table...])

强制使用HashJoin连接。

SELECT
    /*+HINT HashJoin(table1 table2 table3) */
    table1.a
FROM
    table1
    JOIN table2 ON table1.a = table2.a
    JOIN table3 ON table1.a = table3.a;

Join Order

Leading(table1 table2[ table...])

强制Join连接按指定顺序进行。

SELECT /*+HINT Leading(table2 table1) */ table1.a from table1 Join table2 on table1.a = table2.a;

Leading(<Join pair>)

强制定义Join连接的顺序和方向。

说明

Join pair是一对用括号括起来的表或其他连接对,可以形成嵌套结构。

SELECT
/*+HINT Leading((table2 table1) (table3 table4)) */
    *
FROM
    table1
    LEFT JOIN table2 ON table1.a = table2.a
    RIGHT JOIN table3 ON table1.a = table3.a
    LEFT JOIN table4 ON table3.a = table4.a
ORDER BY
    table1.a;

Runtime Filter

RuntimeFilter(table1 table2[ table...])

强制对指定表上的HashJoin使用Runtime Filter。

SELECT /*+HINT RuntimeFilter(table1 table2) */ * FROM table1 JOIN table2 ON table1.a = table2.a;

只能对HashJoin生效。

GUC

Set(GUC-parameter value)

在构造计划时,指定GUC参数值。

说明
  • GUC-parameter表示GUC参数名。

  • value表示参数的取值。

  • Hologres提供的GUC参数,请参见GUC参数

EXPLAIN
SELECT
    /*+HINT set(hg_experimental_enable_reuse_cte_of_count_distinct off) */
    count(DISTINCT a),
    count(DISTINCT b)
FROM table1;

仅对当前Query级别生效,当前Query执行完成后,后续Query将不会受GUC Hint的影响。

使用步骤

  1. 通过GUC参数开启Hint功能。

    根据需要选择在Session级别或DB级别开启,方式如下。

    • Session级别开启。

      SET pg_hint_plan_enable_hint=on;
    • DB级别开启,新建连接后生效。

      ALTER database <dbname> SET pg_hint_plan_enable_hint=on;
  2. 书写Hint。

    使用如下格式书写Hint:

    SELECT|UPDATE|INSERT|DELETE /*+HINT <HintName(params)>  */ ...

    HintName(params)表示Hint关键词及对应参数,详情请参见Hint关键词

    说明
    • Hint关键词不区分大小写。

    • 仅允许直接在INSERT、UPDATE、DELETE和SELECT关键字后指定Hint。

    • Hint内容应位于/*+HINT*/之间。

  3. 执行对应SQL。

使用场景

下述以具体示例为您介绍Hint的使用场景。示例表的DDL语句如下:

CREATE TABLE target (a int primary key, b int);
CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (a int, b int);
CREATE TABLE t3 (a int);
CREATE TABLE t4 (a int);

使用Hint调整Join Order

Join Order主要用于调整表Join的顺序,不合理的Join Order会对SQL查询性能产生极大影响,而造成Join Order不合理的原因通常为统计信息缺失或统计信息不准确。

  • 统计信息缺失:通常是因为未及时执行Analyze操作导致,Analyze详情请参见ANALYZE和AUTO ANALYZE

  • 统计信息不准确:通常发生在进行了过滤或Join操作之后,统计信息过时,导致实际结果集和预估行数发生较大的偏差。

当Join Order不合理时,您可以根据业务实际进行手动使用GUC或者Hint调整。相比GUC的方式,通过Hint调整Join Order会更加简单方便。

如下SQL示例中,Join指的是t1 Join t2,HashJoin需要使用小表构建哈希表(即执行计划中Hash算子下方的部分),如果实际上t2表的行数远大于t1,SQL查询性能会降低。解决方法除更新统计信息(执行Analyze操作)之外,您还可以使用Hint调整Join Order。例如:使用Leading(t2 t1)将Join顺序调整为t2 Join t1后,执行计划更加合理,且执行效率更高。

  • SQL示例

    SELECT /*+HINT Leading(t2 t1) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a;
  • 执行计划对比

    • 未开启Hint的执行计划

      QUERY PLAN                                     
      -----------------------------------------------------------------------------------
       Gather  (cost=0.00..10.07 rows=1000 width=4)
         ->  Hash Join  (cost=0.00..10.05 rows=1000 width=4)
               Hash Cond: (t1.a = t2.a)
               ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                     Hash Key: t1.a
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)
               ->  Hash  (cost=5.01..5.01 rows=1000 width=4)
                     ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                           Hash Key: t2.a
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                 ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=4)
    • 开启Hint的执行计划

      QUERY PLAN                                     
      -----------------------------------------------------------------------------------
       Gather  (cost=0.00..10.07 rows=1000 width=4)
         ->  Hash Join  (cost=0.00..10.05 rows=1000 width=4)
               Hash Cond: (t2.a = t1.a)
               ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                     Hash Key: t2.a
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=4)
               ->  Hash  (cost=5.01..5.01 rows=1000 width=4)
                     ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                           Hash Key: t1.a
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                 ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)

使用GUC Hint

在某些场景中,Query需要使用GUC参数才能达到更好的效果。GUC Hint主要用于设置Query级别的GUC参数,类似于在Query执行前设置GUC参数。通过GUC Hint可以高效的对某个Query设置GUC,Query执行完成后,GUC参数即失效,以便降低对其他Query的影响。

  • SQL示例

    SELECT /*+HINT set(hg_experimental_query_batch_size 512) */t1.a FROM t1 JOIN t2 ON t1.a = t2.a;
  • 执行计划

    QUERY PLAN
    Hash Join  (cost=0.00..10.00 rows=1 width=4)
      Hash Cond: (t1.a = t2.a)
      ->  Gather  (cost=0.00..5.00 rows=1 width=4)
            ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                  ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=4)
      ->  Hash  (cost=5.00..5.00 rows=1 width=4)
            ->  Gather  (cost=0.00..5.00 rows=1 width=4)
                  ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                        ->  Seq Scan on t2  (cost=0.00..5.00 rows=1 width=4)

CTE和子查询的Hint使用

在包含CTE和子查询的场景下,使用Hint影响其执行计划。

  • SQL示例

    WITH c1 AS (
            SELECT /*+HINT Leading(t2 t1) */ t1.a FROM (
                (
                    SELECT /*+HINT leading(t2 t1) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a
                ) AS t1
                JOIN
                (
                    SELECT /*+HINT NestLoop(t4 t3) */ t4.a FROM t3 JOIN t4 ON t3.a = t4.a
                ) AS t2
                ON t1.a = t2.a
            )
        ),
        c2 AS (
            SELECT /*+HINT leading(t1 t2) */ t2.a FROM (
                (
                    SELECT /*+HINT Leading(t1 t2) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a
                ) AS t1
                JOIN
                (
                    SELECT /*+HINT Leading(t4 t3) */ t4.a FROM t3 JOIN t4 ON t3.a = t4.a
                ) AS t2
                ON t1.a = t2.a
            )
        )
        SELECT /*+HINT NestLoop(v2 v1) */  * FROM (
            (
                SELECT /*+HINT Leading (c1 t2) */ c1.a FROM c1 JOIN t2 ON c1.a = t2.a
            ) AS v1
            JOIN
            (
                SELECT /*+HINT Leading (t1 c2) */ c2.a FROM t1 JOIN c2 ON t1.a = c2.a
            ) AS v2
            ON v1.a = v2.a
        )
        ORDER BY v2.a;
  • 执行计划

    QUERY PLAN
    Sort  (cost=0.00..10660048.36 rows=1 width=8)
      Sort Key: t4_1.a
      ->  Gather  (cost=0.00..10660048.36 rows=1 width=8)
            ->  Nested Loop  (cost=0.00..10660048.36 rows=1 width=8)
                  Join Filter: ((t1.a = t4_1.a) AND (t1.a = t1_1.a) AND (t2_1.a = t1_1.a) AND (t2_1.a = t4_1.a))
                  ->  Hash Join  (cost=0.00..25.01 rows=1 width=8)
                        Hash Cond: (t1_1.a = t4_1.a)
                        ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                              Hash Key: t1_1.a
                              ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                    ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=4)
                        ->  Hash  (cost=20.00..20.00 rows=1 width=4)
                              ->  Hash Join  (cost=0.00..20.00 rows=1 width=4)
                                    Hash Cond: ((t1_2.a = t4_1.a) AND (t1_2.a = t3_1.a) AND (t2_2.a = t3_1.a) AND (t2_2.a = t4_1.a))
                                    ->  Hash Join  (cost=0.00..10.00 rows=1 width=8)
                                          Hash Cond: (t1_2.a = t2_2.a)
                                          ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                Hash Key: t1_2.a
                                                ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                      ->  Seq Scan on t1 t1_2  (cost=0.00..5.00 rows=1 width=4)
                                          ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                                ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                      Hash Key: t2_2.a
                                                      ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                            ->  Seq Scan on t2 t2_2  (cost=0.00..5.00 rows=1 width=4)
                                    ->  Hash  (cost=10.00..10.00 rows=1 width=8)
                                          ->  Hash Join  (cost=0.00..10.00 rows=1 width=8)
                                                Hash Cond: (t4_1.a = t3_1.a)
                                                ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                      Hash Key: t4_1.a
                                                      ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                            ->  Seq Scan on t4 t4_1  (cost=0.00..5.00 rows=1 width=4)
                                                ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                                      ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                            Hash Key: t3_1.a
                                                            ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                                  ->  Seq Scan on t3 t3_1  (cost=0.00..5.00 rows=1 width=4)
                  ->  Materialize  (cost=0.00..10385.07 rows=40 width=8)
                        ->  Broadcast  (cost=0.00..10385.07 rows=40 width=8)
                              ->  Hash Join  (cost=0.00..10385.07 rows=1 width=8)
                                    Hash Cond: (t1.a = t2_1.a)
                                    ->  Hash Join  (cost=0.00..10380.07 rows=1 width=4)
                                          Hash Cond: ((t4.a = t1.a) AND (t3.a = t1.a) AND (t3.a = t2.a) AND (t4.a = t2.a))
                                          ->  Redistribution  (cost=0.00..10370.07 rows=1 width=8)
                                                Hash Key: t4.a
                                                ->  Nested Loop  (cost=0.00..10370.07 rows=1 width=8)
                                                      Join Filter: (t3.a = t4.a)
                                                      ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                            ->  Seq Scan on t3  (cost=0.00..5.00 rows=1 width=4)
                                                      ->  Materialize  (cost=0.00..5.00 rows=40 width=4)
                                                            ->  Broadcast  (cost=0.00..5.00 rows=40 width=4)
                                                                  ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                                        ->  Seq Scan on t4  (cost=0.00..5.00 rows=1 width=4)
                                          ->  Hash  (cost=10.00..10.00 rows=1 width=8)
                                                ->  Hash Join  (cost=0.00..10.00 rows=1 width=8)
                                                      Hash Cond: (t2.a = t1.a)
                                                      ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                            Hash Key: t2.a
                                                            ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                                  ->  Seq Scan on t2  (cost=0.00..5.00 rows=1 width=4)
                                                      ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                                            ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                                  Hash Key: t1.a
                                                                  ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                                        ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=4)
                                    ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                          ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                Hash Key: t2_1.a
                                                ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                      ->  Seq Scan on t2 t2_1  (cost=0.00..5.00 rows=1 width=4)

INSERT Hint的使用

通常当目标表与源表有关联,需要进行Join Order或其他相关调整时,在INSERT INTO ... SELECT的场景中使用Hint语法。INSERT INTO ... SELECT的应用场景中,SQL逻辑会比较复杂,需要业务根据计划来添加Hint。

  • 示例1:Hint作用于INSERT目标表和SELECT查询最外层的源表。

    执行过程中,如果t1 Join t2产生的数据量较小,目标表target的数据量较大,当统计信息未更新时,可能无法生成最优的执行计划,可以通过Hint来调整Join Order,实现更好的性能。

    • SQL示例

      --Hint作用在INSERT目标表和SELECT查询最外层的源表
      INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;
    • 执行计划

      QUERY PLAN                                                    
      -----------------------------------------------------------------------------------------------------------------
       Gather  (cost=0.00..26.57 rows=1000 width=8)
         ->  Insert  (cost=0.00..26.54 rows=1000 width=8)
               ->  Project  (cost=0.00..16.12 rows=1000 width=8)
                     ->  Hash Right Join  (cost=0.00..15.12 rows=1000 width=12)
                           Hash Cond: (target.a = t1.a)
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                 ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Hash  (cost=10.07..10.07 rows=1000 width=8)
                                 ->  Redistribution  (cost=0.00..10.07 rows=1000 width=8)
                                       Hash Key: t1.a
                                       ->  Hash Join  (cost=0.00..10.06 rows=1000 width=8)
                                             Hash Cond: (t1.a = t2.a)
                                             ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                                                   Hash Key: t1.a
                                                   ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                                         ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)
                                             ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
                                                   ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                                         Hash Key: t2.a
                                                         ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                                               ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)
  • 示例2:Hint作用于SELECT子查询。

    • SQL示例

      说明

      以下两种INSERT语句使用Hint的场景是等效的。

      INSERT INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;
      
      INSERT /*+HINT Leading(t2 t1) */ INTO target SELECT t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;
    • 执行计划

      QUERY PLAN                                                 
      -----------------------------------------------------------------------------------------------------------
       Gather  (cost=0.00..26.57 rows=1000 width=8)
         ->  Insert  (cost=0.00..26.54 rows=1000 width=8)
               ->  Project  (cost=0.00..16.12 rows=1000 width=8)
                     ->  Hash Left Join  (cost=0.00..15.12 rows=1000 width=12)
                           Hash Cond: (t1.a = target.a)
                           ->  Redistribution  (cost=0.00..10.07 rows=1000 width=8)
                                 Hash Key: t1.a
                                 ->  Hash Join  (cost=0.00..10.06 rows=1000 width=8)
                                       Hash Cond: (t2.a = t1.a)
                                       ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                             Hash Key: t2.a
                                             ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                                   ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)
                                       ->  Hash  (cost=5.01..5.01 rows=1000 width=4)
                                             ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                                                   Hash Key: t1.a
                                                   ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                                         ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Hash  (cost=5.00..5.00 rows=1000 width=4)
                                 ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                       ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=4)

UPDATE HINT的使用

UPDATE语句中HINT通常也用于目标表和源表有关联,需要进行手动调整的情况。

  • SQL示例

    t1表数据量大于target,设置HINT使target表作为哈希表,达到调整Join Order的目的。

    UPDATE /*+HINT Leading(t1 target) */ target SET b=t1.b+1 FROM t1 WHERE t1.a=target.a;
  • 执行计划对比

    • 未开启Hint的执行计划

      QUERY PLAN                                           
      -----------------------------------------------------------------------------------------------
       Gather  (cost=0.00..52.77 rows=1000 width=1)
         ->  Update  (cost=0.00..52.76 rows=1000 width=1)
               ->  Project  (cost=0.00..11.09 rows=1000 width=32)
                     ->  Hash Join  (cost=0.00..10.08 rows=1000 width=32)
                           Hash Cond: (target.a = t1.a)
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=28)
                                 ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=28)
                           ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
                                 ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                       Hash Key: t1.a
                                       ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                             ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)
      
    • 开启Hint的执行计划

      QUERY PLAN                                          
      ----------------------------------------------------------------------------------------------
       Gather  (cost=0.00..52.77 rows=1000 width=1)
         ->  Update  (cost=0.00..52.76 rows=1000 width=1)
               ->  Project  (cost=0.00..11.09 rows=1000 width=32)
                     ->  Hash Join  (cost=0.00..10.08 rows=1000 width=32)
                           Hash Cond: (t1.a = target.a)
                           ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                 Hash Key: t1.a
                                 ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                       ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)
                           ->  Hash  (cost=5.00..5.00 rows=1000 width=28)
                                 ->  Local Gather  (cost=0.00..5.00 rows=1000 width=28)
                                       ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=28)

Runtime Filter Hint的使用

Hologres支持Runtime Filter,当SQL不满足Runtime Filter的生成条件时,可以使用Hint强制生成Runtime Filter,提升查询性能。

重要
  • 只有在执行HashJoin操作时,才能使用Hint强制生成Runtime Filter。

  • 并不是每次强制生成Runtime Filter后,都会提升查询性能,需要根据业务情况综合评估。

  • SQL示例

    SELECT /*+HINT runtimefilter(t1 t2) */ * FROM t1 JOIN t2 ON t1.a = t2.a;
  • 执行计划

    --plan中出现runtime filter,说明触发了runtime filter 
    QUERY PLAN                                     
    -----------------------------------------------------------------------------------
     Gather  (cost=0.00..10.13 rows=1000 width=16)
       ->  Hash Join  (cost=0.00..10.07 rows=1000 width=16)
             Hash Cond: (t1.a = t2.a)
             Runtime Filter Cond: (t1.a = t2.a)
             ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                   Hash Key: t1.a
                   ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                         ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)
                               Runtime Filter Target Expr: t1.a
             ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
                   ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                         Hash Key: t2.a
                         ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                               ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)