GaussDB-同层参数化路径的Hint

GaussDB-同层参数化路径的Hint

功能描述

通过predpush_same_level、nestloop_index hint来指定同层表或物化视图之间参数化路径生成。

跨层参数化路径hint请参见参数化路径的Hint。

语法格式

| ``` predpush_same_level([@queryblock] src, dest) predpush_same_level([@queryblock] src1 src2 ..., dest) [no] nestloop_index([@queryblock] dest[, index_list]) -- 索引方式 [no] nestloop_index([@queryblock] dest[,(src1 src2 ...)]) -- 表名方式

| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

![](https://p6-xtjj-sign.byteimg.com/tos-cn-i-73owjymdk6/045f823e29cc40c1a290f625f830b054~tplv-73owjymdk6-jj-mark-v1:0:0:0:0:5o6Y6YeR5oqA5pyv56S-5Yy6IEAgamVycnl3YW5nMTk4Mw==:q75.awebp?rk3s=f64ab15b&x-expires=1772005045&x-signature=U12%2BwTPYgQmZHxXPjQkKal1jhoY%3D)

predpush_same_level参数仅在rewrite_rule中的predpushforce选项打开时生效。

nestloop_index对rewrite_rule不做要求。

#### 参数说明

-   no表示hint的参数化路径方式不使用。

<!---->

-   @queryblock请参见[指定Hint所处的查询块Queryblock](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0272.html#ZH-CN_TOPIC_0000001911585745)章节，可省略，表示在当前查询块生效。
-   dest为参数化路径的目标表，即索引所在的表。
-   src为参数路径的参数表。
-   index_list为参数化路径使用的索引序列，为空格隔开的字符串。

#### 示例

查看下面的计划示例需要设置以下参数：

set enable_fast_query_shipping = off; set enable_stream_operator = on;

1.  nestloop_index示例：

-   在t1表上传入t2,t3表的t2.c1和t3.c2进行索引扫描（参数化路径）：

    ```
    gaussdb=# explain (costs off) select /*+nestloop_index(t1,(t2 t3)) */* from t1,t2,t3 where t1.c1 = t2.c1 and t1.c2 = t3.c2;
                               QUERY PLAN                            
    -----------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Nested Loop
             ->  Streaming(type: BROADCAST)
                   Spawn on: All datanodes
                   ->  Seq Scan on t3
             ->  Nested Loop
                   ->  Seq Scan on t2
                   ->  Index Scan using it1 on t1
                         Index Cond: ((c1 = t2.c1) AND (c2 = t3.c2))
    (10 rows)
    ```

<!---->

-   在t1表上的it1上进行索引扫描（参数化路径）

    ```
    gaussdb=# explain (costs off) select /*+NestLoop_Index(t1,it1) */* from t1,t2 where t1.c1 = t2.c1;
                   QUERY PLAN               
    ----------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Nested Loop
             ->  Seq Scan on t2
             ->  Index Scan using it1 on t1
                   Index Cond: (c1 = t2.c1)
    (6 rows)
    ```

2.  predpush_same_level示例：

-   准备参数：

    | ```
    gaussdb=# set rewrite_rule = 'predpushforce'; SET gaussdb=# set enable_fast_query_shipping=off; SET 
    ``` |
    | ------------------------------------------------------------------------------------------------------------ |

<!---->

-   执行语句查看计划：

    | ```
    gaussdb=# explain select * from t1, t2 where t1.c1 = t2.c1;                               QUERY PLAN -----------------------------------------------------------------------   Streaming (type: GATHER)  (cost=16.98..34.22 rows=40 width=24)    Node/s: All datanodes    ->  Hash Join  (cost=16.36..32.66 rows=40 width=24)          Hash Cond: (t1.c1 = t2.c1)          ->  Seq Scan on t1  (cost=0.00..16.16 rows=40 width=12)          ->  Hash  (cost=16.16..16.16 rows=40 width=12)                ->  Seq Scan on t2  (cost=0.00..16.16 rows=40 width=12)  (7 rows) 
    ``` |
    | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

<!---->

-   可以看到t1.c1 = t2.c2条件过滤在Join上面，此时可以通过predpush_same_level(t1, t2)将条件下推至t2的扫描算子上：

    | ```
    gaussdb=# explain select /*+predpush_same_level(t1, t2)*/ * from t1, t2 where t1.c1 = t2.c1;                                 QUERY PLAN ---------------------------------------------------------------------------    Streaming (type: GATHER)  (cost=0.62..70.20 rows=40 width=24)    Node/s: All datanodes    ->  Nested Loop  (cost=0.00..68.64 rows=40 width=24)          ->  Seq Scan on t1  (cost=0.00..16.16 rows=40 width=12)          ->  Index Scan using it2 on t2  (cost=0.00..3.27 rows=1 width=12)                Index Cond: (c1 = t1.c1)  (6 rows) 
    ``` |
    | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

![](https://p6-xtjj-sign.byteimg.com/tos-cn-i-73owjymdk6/3ce570ffa3364e0dacb3c09350fcca3b~tplv-73owjymdk6-jj-mark-v1:0:0:0:0:5o6Y6YeR5oqA5pyv56S-5Yy6IEAgamVycnl3YW5nMTk4Mw==:q75.awebp?rk3s=f64ab15b&x-expires=1772005045&x-signature=yQP9sVlHS7tBaXqVCZ1dOnU%2BdIk%3D)NOTICE:

-   可以指定多个src，但是所有的src必须在同一个条件中。
-   如果指定的src和dest条件不存在，或该条件不符合参数化路径要求，则本hint不生效。
-   如果dest扫描算子上存在stream算子，则本hint不生效。

更多详情请参考GaussDB 文档中心：<https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/productdesc/qlh_03_0001.html>