HINT

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

前提条件

使用HINT功能前,请确保您已根据业务需求在Session级别或DB级别设置如下GUC参数:

  • Session级别开启。

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

    ALTER DATABASE <dbname> SET pg_hint_plan_enable_hint=on;

使用限制

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;
    • 错误示例

      --不能在INSERTSELECT后同时使用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)表示t1t2先进行Join,再Join t3,而Leading(t1 (t2 t3))表示先执行t2 Join t3,再和t1进行Join。

  • Join Method HintJoin 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 Filterskew 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计划不满足生成条件时,会导致无法生成执行计划。例如指定两表进行NestLoopRight Join时,由于不支持这样的计划,会报错ERROR: ORCA failed to produce a plan : No plan has been computed for required properties,即属性不满足无法生成计划。

书写格式

使用如下格式书写Hint,然后执行对应的SQL即可:

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

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

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

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

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

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)或视图。

  • Hologres V2.2及以上版本支持。

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生效。

  • Hologres V2.2及以上版本支持。

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的影响。

  • Hologres V2.2及以上版本支持。

Motion Hint

Broadcast(table table[ table...])

强制指定JOIN中,表集合的一侧进行Broadcast。

SELECT /*+HINT Broadcast(t2) Leading(t1 t2) */ * FROM t1 JOIN t2 ON t1.a=t2.a;
  • 通常需要搭配Leading使用。

  • Hologres V3.0及以上版本支持。

NoBroadcast(table table[ table...])

强制指定JOIN中,表集合的一侧不进行Broadcast。

SELECT /*+HINT NoBroadcast(t2) Leading(t1 t2) */ * FROM t1 JOIN t2 ON t1.a=t2.a;

Gather(table table[ table...])

强制指定JOIN中,表集合的一侧进行Gather。

SELECT /*+HINT Gather(t2) Leading(t1 t2) */ * FROM t1 JOIN t2 ON t1.a=t2.a;

NoGather(table table[ table...])

强制指定JOIN中,表集合的一侧不进行Gather。

SELECT /*+HINT NoGather(t2) Leading(t1 t2) */ * FROM t1 JOIN t2 ON t1.a=t2.a;

使用场景

下述以具体示例为您介绍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详情请参见ANALYZEAUTO 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)

Motion HINT的使用

  • 示例1:强制t1表在JOIN时进行Broadcast,通常是在Stats不准确,导致数据Shuffle量非常大的时候使用。由于Hash JOIN目前只能在build端进行Broadcast,因此需要Leading指定JOIN ORDER,避免由于缺少Stats,Broadcastcost惩罚值过大,导致选择t1 JOIN t2的顺序。

    • SQL示例

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

      QUERY PLAN                                          
      ----------------------------------------------------------------------------------------------
        Gather  (cost=0.00..100000000000000005366162204393472.00 rows=1000 width=16)
         ->  Hash Join  (cost=0.00..100000000000000005366162204393472.00 rows=1000 width=16)
               Hash Cond: (t2.a = t1.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=100000000000000005366162204393472.00..100000000000000005366162204393472.00 rows=3000 width=8)
                     ->  Broadcast  (cost=0.00..100000000000000005366162204393472.00 rows=3000 width=8)
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                 ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)
  • 示例2:强制t1表不使用Broadcast。通常适用于采用了Broadcast的表统计信息不准确,导致实际行数很大的表预估结果很少,从而在处理大表时进行Broadcast操作的性能很差的场景。

    • 示例数据

      CREATE TABLE test1(a int, b int);
      CREATE TABLE test2(a int, b int);
      
      INSERT INTO test1 SELECT 1, i FROM generate_series(1, 10) AS i;
      INSERT INTO test2 SELECT 1,i FROM generate_series(1, 1000000) AS i;
      
      analyze test1,test2;
    • SQL示例

      不使用HINT走的Broadcast

      explain SELECT * FROM test1 JOIN test2 ON test1.b = test2.b;

      使用HINT禁止了Broadcast

      explain SELECT /*+HINT NoBroadcast(test1)  */ * FROM test1 JOIN test2 ON test1.b = test2.b;
    • 执行计划

      不使用HINT走的Broadcast

      QUERY PLAN
      ---------------------------------------------------------------------------------
      Gather  (cost=0.00..51.98 rows=1000000 width=16)
        ->  Hash Join  (cost=0.00..13.12 rows=1000000 width=16)
              Hash Cond: (test2.b = test1.b)
              ->  Local Gather  (cost=0.00..5.23 rows=1000000 width=8)
                    ->  Seq Scan on test2  (cost=0.00..5.20 rows=1000000 width=8)
              ->  Hash  (cost=5.00..5.00 rows=200 width=8)
                    ->  Broadcast  (cost=0.00..5.00 rows=200 width=8)
                          ->  Local Gather  (cost=0.00..5.00 rows=10 width=8)
                                ->  Seq Scan on test1  (cost=0.00..5.00 rows=10 width=8)

      使用HINT禁止了Broadcast

      QUERY PLAN
      ---------------------------------------------------------------------------------
      Gather  (cost=0.00..53.23 rows=1000000 width=16)
        ->  Hash Join  (cost=0.00..14.37 rows=1000000 width=16)
              Hash Cond: (test2.b = test1.b)
              ->  Redistribution  (cost=0.00..6.48 rows=1000000 width=8)
                    Hash Key: test2.b
                    ->  Local Gather  (cost=0.00..5.23 rows=1000000 width=8)
                          ->  Seq Scan on test2  (cost=0.00..5.20 rows=1000000 width=8)
              ->  Hash  (cost=5.00..5.00 rows=10 width=8)
                    ->  Redistribution  (cost=0.00..5.00 rows=10 width=8)
                          Hash Key: test1.b
                          ->  Local Gather  (cost=0.00..5.00 rows=10 width=8)
                                ->  Seq Scan on test1  (cost=0.00..5.00 rows=10 width=8)