更新时间:2024-06-03 GMT+08:00

查询改写的Hint

功能描述

优化器支持一系列查询改写规则,可以对SQL语句进行等价的逻辑改写,从而生成更好的执行计划。但在一些场景下,用户并不希望改写SQL语句、或者优化器的改写会导致计划跳变,对于这些特定的场景,需要能够使用hint对改写规则进行控制,让优化器按照特定的方式进行改写。目前数据库支持对ANY/EXISTS的子链接、简单子查询、消减ORDER BY、HAVING子句下推、延迟聚合等多种场景的SQL进行hint控制,具体请参见:Hint使用说明

  • 部分查询改写规则同时受查询改写的hint和GUC参数控制,通常查询改写的hint优先级高于GUC参数控制,涉及到受GUC参数控制的改写规则会在Hint使用说明相关章节进行描述。
  • 每条查询改写规则受一对互斥的hint控制,如:子查询展开的规则同时受EXPAND_SUBQUERY和NO_EXPAND_SUBQUERY控制,其中,EXPAND_SUBQUERY Hint表示允许应用该规则对SQL进行改写,NO_EXPAND_SUBQUERY表示禁止使用该规则对SQL进行改写。且当同一个查询块(queryblock)中同时存在两个互斥的hint时,以获取的首个hint为准,例如:/*+ EXPAND_SUBQUERY NO_EXPAND_SUBQUERY */,则EXPAND_SUBQUERY Hint生效。
  • 查询改写的hint允许重复,但对于重复的hint数据库只会使用第一个,对于其他未使用的hint则会报"unused hint" Warning提示。例如:/*+ EXPAND_SUBLINK EXPAND_SUBLINK */,由于数据库只使用第一个EXPAND_SUBLINK hint,所以仍然会报"unused hint" Warning提示。

语法格式

hintname[(@queryblock)]

参数说明

  • hintname:控制查询改写规则的hint名称,当前支持的查询改写hint请参见表1
  • @queryblock请参见指定Hint所处于的查询块Queryblock,可省略,表示在当前查询块生效,当不指定时,hint没有括号"()"。

查询改写Hint列表

表1 查询改写支持的hint列表

序号

Hint名称

描述

1

EXPAND_SUBLINK_HAVING

允许HAVING子句中的子链接提升。

2

NO_EXPAND_SUBLINK_HAVING

禁止HAVING子句中的子链接提升。

3

EXPAND_SUBLINK

允许对ANY/EXISTS类型子链接进行提升。

4

NO_EXPAND_SUBLINK

禁止对ANY/EXISTS类型子链接进行提升。

5

EXPAND_SUBLINK_TARGET

允许对TargetList中的子链接进行提升。

6

NO_EXPAND_SUBLINK_TARGET

禁止对TargetList中的子链接进行提升。

7

USE_MAGIC_SET

从主查询下推条件到子查询,先针对子查询的关联字段进行分组聚集,再和主查询进行关联,减少相关子链接的重复扫描,提升查询效率。

8

NO_USE_MAGIC_SET

禁止从主查询下推条件到子查询,将带有聚集算子的子查询提前和主查询进行关联。

9

EXPAND_SUBLINK_UNIQUE_CHECK

允许对无agg的子链接进行提升,子链接提升需要保证对于每个条件只有一行输出。

10

NO_EXPAND_SUBLINK_UNIQUE_CHECK

禁止对无agg的子链接进行提升。

11

NO_SUBLINK_DISABLE_REPLICATED

允许带有复制表的fast query shipping或者Stream场景的表达式子链接提升。

12

SUBLINK_DISABLE_REPLICATED

禁止带有复制表的fast query shipping或者Stream场景的表达式子链接提升。

13

NO_SUBLINK_DISABLE_EXPR

允许对表达式类型的子链接进行提升。

14

SUBLINK_DISABLE_EXPR

禁止对表达式类型的子链接进行提升。

15

ENABLE_SUBLINK_ENHANCED

允许子链接提升增强,支持对OR表达式等相关或非相关子链接提升。

16

NO_ENABLE_SUBLINK_ENHANCED

禁用子链接提升增强,禁止对OR表达式等相关或非相关子链接提升。

17

PARTIAL_PUSH

Stream场景支持对listagg和arrayagg添加gather算子。

18

NO_PARTIAL_PUSH

Stream场景禁止对listagg和arrayagg添加gather算子。

19

REDUCE_ORDER_BY

消减冗余的ORDER BY,外层查询对内层查询结果无排序要求时,可以减少不必要的ORDER BY提升查询效率。

20

NO_REDUCE_ORDER_BY

禁止消减不必要的ORDER BY。

21

REMOVE_NOT_NULL

消减不必要的NOT NULL条件,当列属性为NOT NULL时,可以消减查询条件中的IS NOT NULL判断。

22

NO_REMOVE_NOT_NULL

禁止消减IS NOT NULL条件判断。

23

LAZY_AGG

子查询与外层查询存在同样的GROUP BY条件,两层聚集运算可能导致查询效率低下,消除子查询中的聚集运算,以此提高查询效率。

24

NO_LAZY_AGG

禁用消除子查询中的聚集运算规则。

25

EXPAND_SUBQUERY

子查询提升,将子查询提升与上层做JOIN连接,优化查询效率。

26

NO_EXPAND_SUBQUERY

禁用子查询提升。

27

PUSHDOWN_HAVING

下推HAVING条件表达式。

28

NO_PUSHDOWN_HAVING

禁止下推HAVING表达式。

29

INLIST_TO_JOIN

控制使用inlist-to-join对SQL进行改写。

30

NO_INLIST_TO_JOIN

控制禁止使用inlist-to-join对SQL进行改写。

31

ROWNUM_PUSHDOWN

允许行号下推。

32

NO_ROWNUM_PUSHDOWN

禁止行号下推。

33

WINDOWAGG_PUSHDOWN

允许父查询中窗口函数的过滤条件下推到子查询。

34

NO_WINDOWAGG_PUSHDOWN

禁止父查询中窗口函数的过滤条件下推到子查询。

Hint使用准备

为了方便了解hint的使用场景,手册提供了所有查询改写hint的应用示例,请参见Hint使用说明,相关建表语句和环境准备如下:

  • 会话设置:
    SET client_encoding = 'UTF8';
    CREATE SCHEMA rewrite_rule_test;
    SET current_schema = rewrite_rule_test;
    SET enable_codegen= off;
  • 建表语句:
    CREATE TABLE rewrite_rule_hint_t1 (a INT, b INT, c INT, d INT);
    CREATE TABLE rewrite_rule_hint_t2 (a INT, b INT, c INT, d INT);
    CREATE TABLE rewrite_rule_hint_t3 (a INT, b INT, c INT, d INT);
    CREATE TABLE rewrite_rule_hint_t4 (a INT NOT NULL, b INT, c INT, d INT);
    CREATE TABLE rewrite_rule_hint_t5 (slot INTEGER NOT NULL,cid BIGINT NOT NULL,name CHARACTER VARYING NOT NULL) WITH (ORIENTATION = row);
    INSERT INTO rewrite_rule_hint_t5 (slot, cid, name) values(generate_series(1, 10),generate_series(1, 10),'records.storage.state');
    ANALYZE rewrite_rule_hint_t5;
    CREATE TABLE rewrite_rule_hint_customer (
        c_custkey INTEGER NOT NULL,
        c_name CHARACTER VARYING(25) NOT NULL,
        c_address CHARACTER VARYING(40) NOT NULL,
        c_nationkey INTEGER NOT NULL,
        c_phone CHARACTER(15) NOT NULL,
        c_acctbal NUMERIC(15, 2) NOT NULL,
        c_mktsegment CHARACTER(10) NOT NULL,
        c_comment CHARACTER VARYING(117) NOT NULL
    );
    CREATE TABLE rewrite_rule_hint_orders (
        o_orderkey INTEGER NOT NULL,
        o_custkey INTEGER NOT NULL,
        o_orderstatus CHARACTER(1) NOT NULL,
        o_totalprice NUMERIC(15, 2) NOT NULL,
        o_orderdate DATE NOT NULL,
        o_orderpriority CHARACTER(15) NOT NULL,
        o_clerk CHARACTER(15) NOT NULL,
        o_shippriority INTEGER NOT NULL,
        o_comment CHARACTER VARYING(79) NOT NULL
    );

Hint使用说明

  1. EXPAND_SUBLINK_HAVING

    允许HAVING子句中的子链接提升。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值为enable_sublink_pullup_enhanced时,规则同样生效。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_EXPAND_SUBLINK_HAVING为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT /*+EXPAND_SUBLINK_HAVING*/ a,sum(b) AS value FROM rewrite_rule_hint_t1 GROUP BY a HAVING sum(a) >= (SELECT avg(b) FROM rewrite_rule_hint_t1) ORDER BY value DESC;
                                              QUERY PLAN
    ----------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Merge Sort Key: inner_subquery.value DESC
       Node/s: All datanodes
       InitPlan 1 (returns $0)
         ->  Aggregate
               ->  Streaming(type: BROADCAST)
                     Spawn on: All datanodes
                     ->  Aggregate
                           ->  Seq Scan on rewrite_rule_hint_t1
       ->  Sort
             Sort Key: inner_subquery.value DESC
             ->  Subquery Scan on inner_subquery
                   ->  HashAggregate
                         Group By Key: rewrite_rule_test.rewrite_rule_hint_t1.a
                         Filter: ((sum(rewrite_rule_test.rewrite_rule_hint_t1.a))::numeric >= $0)
                         ->  Seq Scan on rewrite_rule_hint_t1
    (16 rows)
  2. NO_EXPAND_SUBLINK_HAVING

    禁止HAVING子句中的子链接提升。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值未设置为enable_sublink_pullup_enhanced时,规则同样禁用。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与EXPAND_SUBLINK_HAVING为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT /*+NO_EXPAND_SUBLINK_HAVING*/ a,sum(b) AS value FROM rewrite_rule_hint_t1 GROUP BY a HAVING sum(a) >= (SELECT avg(b) FROM rewrite_rule_hint_t1) ORDER BY value DESC;
                                           QUERY PLAN
    ----------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Merge Sort Key: (sum(rewrite_rule_test.rewrite_rule_hint_t1.b)) DESC
       Node/s: All datanodes
       InitPlan 1 (returns $0)
         ->  Aggregate
               ->  Streaming(type: BROADCAST)
                     Spawn on: All datanodes
                     ->  Aggregate
                           ->  Seq Scan on rewrite_rule_hint_t1
       ->  Sort
             Sort Key: (sum(rewrite_rule_test.rewrite_rule_hint_t1.b)) DESC
             ->  HashAggregate
                   Group By Key: rewrite_rule_test.rewrite_rule_hint_t1.a
                   Filter: ((sum(rewrite_rule_test.rewrite_rule_hint_t1.a))::numeric >= $0)
                   ->  Seq Scan on rewrite_rule_hint_t1
    (15 rows)
  3. EXPAND_SUBLINK

    允许子链接进行提升。支持对[Not]Any类型的非相关子链接或[Not]Exists类型的相关子链接等场景的控制。该场景下本规则的hint与NO_EXPAND Hint互斥,且该hint的优先级高于NO_EXPAND。该hint与NO_EXPAND_SUBLINK为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT * FROM rewrite_rule_hint_t1 WHERE a > ANY(SELECT /*+EXPAND_SUBLINK*/ a FROM rewrite_rule_hint_t2) AND b > ANY (SELECT /*+EXPAND_SUBLINK*/a FROM rewrite_rule_hint_t3);
                                      QUERY PLAN
    ------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Nested Loop Semi Join
             Join Filter: (rewrite_rule_hint_t1.b > rewrite_rule_hint_t3.a)
             ->  Nested Loop Semi Join
                   Join Filter: (rewrite_rule_hint_t1.a > rewrite_rule_hint_t2.a)
                   ->  Seq Scan on rewrite_rule_hint_t1
                   ->  Materialize
                         ->  Streaming(type: BROADCAST)
                               Spawn on: All datanodes
                               ->  Seq Scan on rewrite_rule_hint_t2
             ->  Materialize
                   ->  Streaming(type: BROADCAST)
                         Spawn on: All datanodes
                         ->  Seq Scan on rewrite_rule_hint_t3
    (15 rows)
  4. NO_EXPAND_SUBLINK

    禁止子链接进行提升。支持对[Not]Any类型的非相关子链接或[Not]Exists类型的相关子链接等场景的控制。该场景下本规则的hint与NO_EXPAND Hint等效。该hint与EXPAND_SUBLINK为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT * FROM rewrite_rule_hint_t1 WHERE a > ANY(SELECT /*+NO_EXPAND_SUBLINK*/ a FROM rewrite_rule_hint_t2) AND b > ANY (SELECT /*+EXPAND_SUBLINK*/a FROM rewrite_rule_hint_t3);
                             QUERY PLAN
    ------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Seq Scan on rewrite_rule_hint_t1
             Filter: ((NOT (hashed SubPlan 2)) AND (SubPlan 1))
             SubPlan 2
               ->  Materialize
                     ->  Streaming(type: BROADCAST)
                           Spawn on: All datanodes
                           ->  Seq Scan on rewrite_rule_hint_t3
             SubPlan 1
               ->  Materialize
                     ->  Streaming(type: BROADCAST)
                           Spawn on: All datanodes
                           ->  Seq Scan on rewrite_rule_hint_t2
    (14 rows)
  5. EXPAND_SUBLINK_TARGET

    允许TargetList中的子链接进行提升。该场景下本规则的hint与NO_EXPAND Hint互斥,且该hint优先级高于NO_EXPAND。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值为intargetlist时,规则同样生效。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_EXPAND_SUBLINK_TARGET为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT a,(SELECT /*+EXPAND_SUBLINK_TARGET*/ avg(b) FROM rewrite_rule_hint_t1 WHERE rewrite_rule_hint_t1.b = rewrite_rule_hint_t2.b) FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t2.a < 100 ORDER BY rewrite_rule_hint_t2.b;
                                                       QUERY PLAN
    ----------------------------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Merge Sort Key: rewrite_rule_test.rewrite_rule_hint_t2.b
       Node/s: All datanodes
       ->  Merge Left Join
             Merge Cond: (rewrite_rule_test.rewrite_rule_hint_t2.b = rewrite_rule_hint_t1.b)
             ->  Sort
                   Sort Key: rewrite_rule_test.rewrite_rule_hint_t2.b
                   ->  Streaming(type: REDISTRIBUTE)
                         Spawn on: All datanodes
                         ->  Seq Scan on rewrite_rule_hint_t2
                               Filter: (a < 100)
             ->  Sort
                   Sort Key: rewrite_rule_hint_t1.b
                   ->  HashAggregate
                         Group By Key: rewrite_rule_hint_t1.b
                         ->  Streaming(type: REDISTRIBUTE)
                               Spawn on: All datanodes
                               ->  Hash Right Semi Join
                                     Hash Cond: (rewrite_rule_test.rewrite_rule_hint_t2.b = rewrite_rule_hint_t1.b)
                                     ->  Streaming(type: BROADCAST)
                                           Spawn on: All datanodes
                                           ->  Seq Scan on rewrite_rule_hint_t2
                                                 Filter: (a < 100)
                                     ->  Hash
                                           ->  Seq Scan on rewrite_rule_hint_t1
    (25 rows)
  6. NO_EXPAND_SUBLINK_TARGET

    禁止TargetList中的子链接进行提升。该场景下本规则的hint与NO_EXPAND Hint等效。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值未设置为intargetlist时,规则同样禁用。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与EXPAND_SUBLINK_TARGET为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT a,(SELECT /*+NO_EXPAND_SUBLINK_TARGET*/ avg(b) FROM rewrite_rule_hint_t1 WHERE rewrite_rule_hint_t1.b = rewrite_rule_hint_t2.b) FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t2.a < 100 ORDER BY rewrite_rule_hint_t2.b;
                                          QUERY PLAN
    ---------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Merge Sort Key: rewrite_rule_hint_t2.b
       Node/s: All datanodes
       ->  Sort
             Sort Key: rewrite_rule_hint_t2.b
             ->  Seq Scan on rewrite_rule_hint_t2
                   Filter: (a < 100)
                   SubPlan 1
                     ->  Aggregate
                           ->  Result
                                 Filter: (rewrite_rule_hint_t1.b = rewrite_rule_hint_t2.b)
                                 ->  Materialize
                                       ->  Streaming(type: BROADCAST)
                                             Spawn on: All datanodes
                                             ->  Seq Scan on rewrite_rule_hint_t1
    (15 rows)
  7. USE_MAGIC_SET

    从主查询下推条件到子查询。先针对子查询的关联字段进行分组聚集,再和主查询进行关联,减少相关子链接的重复扫描,提升查询效率。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值设置为magicset时,规则同样生效。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_USE_MAGIC_SET为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off) SELECT rewrite_rule_hint_t1 FROM rewrite_rule_hint_t1 WHERE rewrite_rule_hint_t1.b = 10 AND rewrite_rule_hint_t1.c < (SELECT /*+USE_MAGIC_SET*/ sum(c) FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.a);
                                                                                 QUERY PLAN
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Nested Loop
       Join Filter: ((rewrite_rule_test.rewrite_rule_hint_t1.c < (sum(rewrite_rule_hint_t2.c))) AND (rewrite_rule_test.rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.a))
       ->  Data Node Scan on rewrite_rule_hint_t1 "_REMOTE_TABLE_QUERY_"
             Node/s: All datanodes
       ->  HashAggregate
             Group By Key: rewrite_rule_hint_t2.a
             ->  Hash Semi Join
                   Hash Cond: (rewrite_rule_hint_t2.a = rewrite_rule_test.rewrite_rule_hint_t1.a)
                   ->  Data Node Scan on rewrite_rule_hint_t2 "_REMOTE_TABLE_QUERY_"
                         Node/s: All datanodes
                   ->  Hash
                         ->  Data Node Scan on rewrite_rule_hint_t1 "_REMOTE_TABLE_QUERY_"
                               Node/s: All datanodes
    (13 rows)
  8. NO_USE_MAGIC_SET

    禁止从主查询下推条件到子查询。将带有聚集算子的子查询提前和主查询进行关联。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值未设置magicset时,规则同样禁用。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与USE_MAGIC_SET为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off) SELECT rewrite_rule_hint_t1 FROM rewrite_rule_hint_t1 WHERE rewrite_rule_hint_t1.b = 10 AND rewrite_rule_hint_t1.c < (SELECT /*+NO_USE_MAGIC_SET*/ sum(c) FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.a);
                                    QUERY PLAN
    ---------------------------------------------------------------------------
     Hash Join
       Hash Cond: (rewrite_rule_hint_t2.a = rewrite_rule_hint_t1.a)
       Join Filter: (rewrite_rule_hint_t1.c < (sum(rewrite_rule_hint_t2.c)))
       ->  Data Node Scan on "__REMOTE_GROUP_QUERY__"
             Node/s: All datanodes
       ->  Hash
             ->  Data Node Scan on rewrite_rule_hint_t1 "_REMOTE_TABLE_QUERY_"
                   Node/s: All datanodes
    (8 rows)
  9. EXPAND_SUBLINK_UNIQUE_CHECK

    提升无agg的子链接。子链接提升需要保证对于每个条件只有一行输出,对于有agg的子链接可以自动提升。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值为 uniquecheck时,规则同样生效。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_EXPAND_SUBLINK_UNIQUE_CHECK为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT rewrite_rule_hint_t1.a FROM rewrite_rule_hint_t1 WHERE rewrite_rule_hint_t1.a = (SELECT /*+EXPAND_SUBLINK_UNIQUE_CHECK*/ rewrite_rule_hint_t2.a FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.b);
                                         QUERY PLAN
    -------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Hash Join
             Hash Cond: (rewrite_rule_hint_t1.a = subquery."?column?")
             ->  Seq Scan on rewrite_rule_hint_t1
             ->  Hash
                   ->  Subquery Scan on subquery
                         ->  HashAggregate
                               Group By Key: rewrite_rule_hint_t2.b
                               Filter: (rewrite_rule_hint_t2.b = rewrite_rule_hint_t2.a)
                               Unique Check Required
                               ->  Seq Scan on rewrite_rule_hint_t2
    (12 rows)
  10. NO_EXPAND_SUBLINK_UNIQUE_CHECK

    禁止提升无agg的子链接。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值未设置为uniquecheck时,规则同样禁用。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与EXPAND_SUBLINK_UNIQUE_CHECK为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT rewrite_rule_hint_t1.a FROM rewrite_rule_hint_t1 WHERE rewrite_rule_hint_t1.a = (SELECT /*+NO_EXPAND_SUBLINK_UNIQUE_CHECK*/ rewrite_rule_hint_t2.a FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.b);
                                    QUERY PLAN
    ---------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Seq Scan on rewrite_rule_hint_t1
             Filter: (a = (SubPlan 1))
             SubPlan 1
               ->  Result
                     Filter: (rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.b)
                     ->  Materialize
                           ->  Streaming(type: BROADCAST)
                                 Spawn on: All datanodes
                                 ->  Seq Scan on rewrite_rule_hint_t2
    (11 rows)
  11. NO_SUBLINK_DISABLE_REPLICATED

    不禁止带有复制表的fast query shipping或者Stream场景提升子链接。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值未设置为disablerep时,规则生效。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与SUBLINK_DISABLE_REPLICATED为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT * FROM rewrite_rule_hint_t1 WHERE (0 =(SELECT /*+NO_SUBLINK_DISABLE_REPLICATED*/ count(*) FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t2.a = rewrite_rule_hint_t1.a) OR NOT EXISTS(SELECT /*+NO_SUBLINK_DISABLE_REPLICATED*/1 FROM rewrite_rule_hint_t3 WHERE rewrite_rule_hint_t3.b = rewrite_rule_hint_t1.b));
                                                               QUERY PLAN
    ---------------------------------------------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Hash Left Join
             Hash Cond: (rewrite_rule_hint_t1.b = rewrite_rule_hint_t3.b)
             Filter: (((subquery."?column?" IS NOT NULL) AND (0 = COALESCE(subquery.count, 0))) OR (rewrite_rule_hint_t3.b IS NULL))
             ->  Streaming(type: REDISTRIBUTE)
                   Spawn on: All datanodes
                   ->  Hash Left Join
                         Hash Cond: (rewrite_rule_hint_t1.a = subquery."?column?")
                         ->  Seq Scan on rewrite_rule_hint_t1
                         ->  Hash
                               ->  Subquery Scan on subquery
                                     ->  HashAggregate
                                           Group By Key: rewrite_rule_hint_t2.a
                                           ->  Seq Scan on rewrite_rule_hint_t2
             ->  Hash
                   ->  HashAggregate
                         Group By Key: rewrite_rule_hint_t3.b
                         ->  Streaming(type: REDISTRIBUTE)
                               Spawn on: All datanodes
                               ->  Seq Scan on rewrite_rule_hint_t3
    (21 rows)
  12. SUBLINK_DISABLE_REPLICATED

    禁止带有复制表的fast query shipping或者Stream场景提升子链接。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值设置为disablerep时,规则禁用。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_SUBLINK_DISABLE_REPLICATED为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT * FROM rewrite_rule_hint_t1 WHERE (0 =(SELECT /*+SUBLINK_DISABLE_REPLICATED*/ count(*) FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t2.a = rewrite_rule_hint_t1.a) OR NOT EXISTS(SELECT /*+NO_SUBLINK_DISABLE_REPLICATED*/1 FROM rewrite_rule_hint_t3 WHERE rewrite_rule_hint_t3.b = rewrite_rule_hint_t1.b));
                                          QUERY PLAN                                      
    --------------------------------------------------------------------------------------
     Seq Scan on rewrite_rule_hint_t1
       Filter: ((0 = (SubPlan 1)) OR (NOT (alternatives: SubPlan 2 or hashed SubPlan 3)))
       SubPlan 1
         ->  Aggregate
               ->  Seq Scan on rewrite_rule_hint_t2
                     Filter: (a = rewrite_rule_hint_t1.a)
       SubPlan 2
         ->  Seq Scan on rewrite_rule_hint_t3
               Filter: (b = rewrite_rule_hint_t1.b)
       SubPlan 3
         ->  Seq Scan on rewrite_rule_hint_t3
    (11 rows)
  13. NO_SUBLINK_DISABLE_EXPR

    允许子链接中表达式提升。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值未设置为disable_pullup_expr_sublink时,规则同样生效。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与SUBLINK_DISABLE_EXPR为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT a FROM rewrite_rule_hint_t1 WHERE rewrite_rule_hint_t1.b = (SELECT /*+NO_SUBLINK_DISABLE_EXPR*/ max(b) FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t2.a = rewrite_rule_hint_t1.a);
                                                       QUERY PLAN
    -----------------------------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Hash Join
             Hash Cond: ((rewrite_rule_hint_t1.a = subquery."?column?") AND (rewrite_rule_hint_t1.b = subquery.max))
             ->  Seq Scan on rewrite_rule_hint_t1
             ->  Hash
                   ->  Subquery Scan on subquery
                         ->  HashAggregate
                               Group By Key: rewrite_rule_hint_t2.a
                               ->  Seq Scan on rewrite_rule_hint_t2
    (10 rows)
  14. SUBLINK_DISABLE_EXPR

    禁止子链接中表达式提升。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值为disable_pullup_expr_sublink时,规则同样禁用。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_SUBLINK_DISABLE_EXPR为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT a FROM rewrite_rule_hint_t1 WHERE rewrite_rule_hint_t1.b = (SELECT /*+SUBLINK_DISABLE_EXPR*/ max(b) FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t2.a = rewrite_rule_hint_t1.a);
                                       QUERY PLAN
    ---------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Seq Scan on rewrite_rule_hint_t1
             Filter: (b = (SubPlan 1))
             SubPlan 1
               ->  Aggregate
                     ->  Result
                           Filter: (rewrite_rule_hint_t2.a = rewrite_rule_hint_t1.a)
                           ->  Materialize
                                 ->  Streaming(type: BROADCAST)
                                       Spawn on: All datanodes
                                       ->  Seq Scan on rewrite_rule_hint_t2
    (12 rows)
  15. ENABLE_SUBLINK_ENHANCED

    子链接提升增强。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值设置为enable_sublink_pullup_enhanced时,规则同样生效。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_ENABLE_SUBLINK_ENHANCED为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN (costs off)SELECT cntrycode,count(*) AS numcust,sum(c_acctbal) AS totacctbal FROM (SELECT substring(c_phone from 1 for 2) AS cntrycode,c_acctbal FROM rewrite_rule_hint_customer WHERE substring(c_phone from 1 for 2) IN ('22', '25', '26', '14', '18', '30', '17')AND c_acctbal > (SELECT /*+ENABLE_SUBLINK_ENHANCED*/ avg(c_acctbal) FROM rewrite_rule_hint_customer WHERE c_acctbal > 0.00 AND substring(c_phone from 1 for 2) IN ('22', '25', '26', '14', '18', '30', '17')) AND NOT EXISTS (SELECT * FROM rewrite_rule_hint_orders WHERE o_custkey = c_custkey)) AS custsale GROUP BY cntrycode ORDER BY cntrycode;
                                                                                           QUERY PLAN
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Sort
       Sort Key: ("substring"((rewrite_rule_test.rewrite_rule_hint_customer.c_phone)::text, 1, 2))
       ->  HashAggregate
             Group By Key: ("substring"((rewrite_rule_test.rewrite_rule_hint_customer.c_phone)::text, 1, 2))
             ->  Streaming (type: GATHER)
                   Node/s: All datanodes
                   ->  HashAggregate
                         Group By Key: "substring"((rewrite_rule_test.rewrite_rule_hint_customer.c_phone)::text, 1, 2)
                         ->  Nested Loop Anti Join
                               Join Filter: (rewrite_rule_hint_orders.o_custkey = rewrite_rule_test.rewrite_rule_hint_customer.c_custkey)
                               ->  Nested Loop
                                     Join Filter: (rewrite_rule_test.rewrite_rule_hint_customer.c_acctbal > (pg_catalog.avg((avg(rewrite_rule_test.rewrite_rule_hint_customer.c_acctbal)))))
                                     ->  Aggregate
                                           ->  Streaming(type: BROADCAST)
                                                 Spawn on: All datanodes
                                                 ->  Aggregate
                                                       ->  Seq Scan on rewrite_rule_hint_customer
                                                             Filter: ((c_acctbal > 0.00) AND ("substring"((c_phone)::text, 1, 2) = ANY ('{22,25,26,14,18,30,17}'::text[])))
                                     ->  Seq Scan on rewrite_rule_hint_customer
                                           Filter: ("substring"((c_phone)::text, 1, 2) = ANY ('{22,25,26,14,18,30,17}'::text[]))
                               ->  Materialize
                                     ->  Streaming(type: REDISTRIBUTE)
                                           Spawn on: All datanodes
                                           ->  Seq Scan on rewrite_rule_hint_orders
    (24 rows)
  16. NO_ENABLE_SUBLINK_ENHANCED

    禁用子链接提升增强。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的Hint且rewrite_rule值未设置为enable_sublink_pullup_enhanced时,规则禁用。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该HintBLE_SUBLINK_ENHANCED为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN (costs off)SELECT cntrycode,count(*) AS numcust,sum(c_acctbal) AS totacctbal FROM (SELECT substring(c_phone from 1 for 2) AS cntrycode,c_acctbal FROM rewrite_rule_hint_customer WHERE substring(c_phone from 1 for 2) IN ('22', '25', '26', '14', '18', '30', '17')AND c_acctbal > (SELECT /*+NO_ENABLE_SUBLINK_ENHANCED*/ avg(c_acctbal) FROM rewrite_rule_hint_customer WHERE c_acctbal > 0.00 AND substring(c_phone from 1 for 2) IN ('22', '25', '26', '14', '18', '30', '17')) AND NOT EXISTS (SELECT * FROM rewrite_rule_hint_orders WHERE o_custkey = c_custkey)) AS custsale GROUP BY cntrycode ORDER BY cntrycode;
                                                                      QUERY PLAN
    ----------------------------------------------------------------------------------------------------------------------------------------------
     Sort
       Sort Key: ("substring"((rewrite_rule_test.rewrite_rule_hint_customer.c_phone)::text, 1, 2))
       InitPlan 1 (returns $0)
         ->  Aggregate
               ->  Streaming(type: BROADCAST)
                     Spawn on: All datanodes
                     ->  Aggregate
                           ->  Seq Scan on rewrite_rule_hint_customer
                                 Filter: ((c_acctbal > 0.00) AND ("substring"((c_phone)::text, 1, 2) = ANY ('{22,25,26,14,18,30,17}'::text[])))
       ->  HashAggregate
             Group By Key: ("substring"((rewrite_rule_test.rewrite_rule_hint_customer.c_phone)::text, 1, 2))
             ->  Streaming (type: GATHER)
                   Node/s: All datanodes
                   ->  HashAggregate
                         Group By Key: "substring"((rewrite_rule_test.rewrite_rule_hint_customer.c_phone)::text, 1, 2)
                         ->  Nested Loop Anti Join
                               Join Filter: (rewrite_rule_hint_orders.o_custkey = rewrite_rule_test.rewrite_rule_hint_customer.c_custkey)
                               ->  Seq Scan on rewrite_rule_hint_customer
                                     Filter: ((c_acctbal > $0) AND ("substring"((c_phone)::text, 1, 2) = ANY ('{22,25,26,14,18,30,17}'::text[])))
                               ->  Materialize
                                     ->  Streaming(type: REDISTRIBUTE)
                                           Spawn on: All datanodes
                                           ->  Seq Scan on rewrite_rule_hint_orders
    (23 rows)
  17. PARTIAL_PUSH

    Stream场景支持对listagg和arrayagg添加gather算子。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值设置为partialpush时,规则同样生效。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_PARTIAL_PUSH为一组互斥的规则控制hint。

    gaussdb=# SET rewrite_rule='intargetlist';
    EXPLAIN (costs off)SELECT /*+PARTIAL_PUSH*/listagg((SELECT b FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t2.b = rewrite_rule_hint_t1.b ORDER BY rewrite_rule_hint_t2.c limit 1), ',') WITHIN GROUP(ORDER BY rewrite_rule_hint_t1.b) FROM rewrite_rule_hint_t1 ORDER BY 1;
                                                    QUERY PLAN
    ------------------------------------------------------------------------------------------------------------
     Sort
       Sort Key: (listagg(subquery.b, ','::text ) WITHIN GROUP ( ORDER BY rewrite_rule_hint_t1.b))
       ->  Aggregate
             ->  Streaming (type: GATHER)
                   Node/s: All datanodes
                   ->  Nested Loop Left Join
                         Join Filter: (subquery."?column?" = rewrite_rule_hint_t1.b)
                         ->  Seq Scan on rewrite_rule_hint_t1
                         ->  Materialize
                               ->  Streaming(type: BROADCAST)
                                     Spawn on: All datanodes
                                     ->  Subquery Scan on subquery
                                           Filter: (subquery."?column?" OPERATOR(pg_catalog.=) 1::bigint)
                                           ->  WindowAgg
                                                 ->  Sort
                                                       Sort Key: rewrite_rule_hint_t2.b, rewrite_rule_hint_t2.c
                                                       ->  Streaming(type: REDISTRIBUTE)
                                                             Spawn on: All datanodes
                                                             ->  Seq Scan on rewrite_rule_hint_t2
    (19 rows)
  18. NO_PARTIAL_PUSH

    Stream场景禁止对listagg和arrayagg添加gather算子。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值未设置为partialpush时,规则禁用。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与PARTIAL_PUSH为一组互斥的规则控制hint。

    gaussdb=# SET rewrite_rule='intargetlist';
    EXPLAIN (costs off)SELECT /*+NO_PARTIAL_PUSH*/listagg((SELECT b FROM rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t2.b = rewrite_rule_hint_t1.b ORDER BY rewrite_rule_hint_t2.c limit 1), ',') WITHIN GROUP(ORDER BY rewrite_rule_hint_t1.b) FROM rewrite_rule_hint_t1 ORDER BY 1;
     
                                                   QUERY PLAN
    ---------------------------------------------------------------------------------------------------------
     Sort
       Sort Key: (listagg(subquery.b, ','::text ) WITHIN GROUP ( ORDER BY rewrite_rule_hint_t1.b))
       ->  Aggregate
             ->  Hash Left Join
                   Hash Cond: (rewrite_rule_hint_t1.b = subquery."?column?")
                   ->  Data Node Scan on rewrite_rule_hint_t1 "_REMOTE_TABLE_QUERY_"
                         Node/s: All datanodes
                   ->  Hash
                         ->  Subquery Scan on subquery
                               Filter: (subquery."?column?" OPERATOR(pg_catalog.=) 1::bigint)
                               ->  WindowAgg
                                     ->  Sort
                                           Sort Key: rewrite_rule_hint_t2.b, rewrite_rule_hint_t2.c
                                           ->  Data Node Scan on rewrite_rule_hint_t2 "_REMOTE_TABLE_QUERY_"
                                                 Node/s: All datanodes
    (15 rows)
  19. REDUCE_ORDER_BY

    消减不必要的ORDER BY。当外层查询对内层查询结果无排序要求时,可以减少不必要的ORDER BY提升查询效率。该hint与NO_REDUCE_ORDER_BY为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT * FROM rewrite_rule_hint_t1,(SELECT /*+REDUCE_ORDER_BY*/ * FROM rewrite_rule_hint_t2 ORDER BY a DESC);
                         QUERY PLAN
    ----------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Nested Loop
             ->  Streaming(type: BROADCAST)
                   Spawn on: All datanodes
                   ->  Seq Scan on rewrite_rule_hint_t1
             ->  Materialize
                   ->  Seq Scan on rewrite_rule_hint_t2
    (8 rows)
  20. NO_REDUCE_ORDER_BY

    禁止消减不必要的ORDER BY。该hint与REDUCE_ORDER_BY为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT * FROM rewrite_rule_hint_t1,(SELECT /*+NO_REDUCE_ORDER_BY*/ * FROM rewrite_rule_hint_t2 ORDER BY a DESC);
                            QUERY PLAN
    -----------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Nested Loop
             ->  Streaming(type: BROADCAST)
                   Spawn on: All datanodes
                   ->  Seq Scan on rewrite_rule_hint_t1
             ->  Materialize
                   ->  Sort
                         Sort Key: rewrite_rule_hint_t2.a DESC
                         ->  Seq Scan on rewrite_rule_hint_t2
    (10 rows)
  21. REMOVE_NOT_NULL

    消减不必要的IS NOT NULL条件。当列属性为NOT NULL时可以消减查询条件中的IS NOT NULL判断。该场景同时受GUC参数enable_constraint_optimization控制,当未使用本规则的hint且enable_constraint_optimization值为on时,规则生效。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_REMOVE_NOT_NULL为一组互斥的规则控制hint。

    gaussdb=# SET enable_fast_query_shipping=off;
    EXPLAIN(costs off)SELECT /*+REMOVE_NOT_NULL*/ * FROM rewrite_rule_hint_t4 WHERE b > 10 OR a IS NOT NULL;
                   QUERY PLAN
    ----------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Seq Scan on rewrite_rule_hint_t4
    (3 rows)
  22. NO_REMOVE_NOT_NULL

    禁止消减不必要的IS NOT NULL条件。该场景同时受GUC参数enable_constraint_optimization控制,当未使用本规则的hint且enable_constraint_optimization值为off时,规则禁用。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与REMOVE_NOT_NULL为一组互斥的规则控制hint。

    gaussdb=# SET enable_fast_query_shipping=off;
    EXPLAIN(costs off)SELECT /*+NO_REMOVE_NOT_NULL*/ * FROM rewrite_rule_hint_t4 WHERE b > 10 OR a IS NOT NULL;
                     QUERY PLAN
    -----------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Seq Scan on rewrite_rule_hint_t4
             Filter: ((b > 10) OR (a IS NOT NULL))
    (4 rows)
  23. LAZY_AGG

    子查询与外层查询存在同样的GROUP BY条件。两层聚集运算可能导致查询效率低下,消除子查询中的聚集运算,以此提高查询效率。该场景同时受GUC参数rewrite_rule控制,当未使用本规则的hint且rewrite_rule值为lazyagg时,规则生效。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_LAZY_AGG为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT rewrite_rule_hint_t1.b,sum(cc) FROM (SELECT /*+LAZY_AGG*/b,sum(c) AS cc FROM rewrite_rule_hint_t2 GROUP BY b) s1,rewrite_rule_hint_t1 WHERE s1.b = rewrite_rule_hint_t1.b GROUP BY rewrite_rule_hint_t1.b ORDER BY 1,2;
                                        QUERY PLAN
    -----------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Merge Sort Key: rewrite_rule_hint_t1.b, (sum((rewrite_rule_hint_t2.c)::bigint))
       Node/s: All datanodes
       ->  Sort
             Sort Key: rewrite_rule_hint_t1.b, (sum((rewrite_rule_hint_t2.c)::bigint))
             ->  HashAggregate
                   Group By Key: rewrite_rule_hint_t1.b
                   ->  Hash Join
                         Hash Cond: (rewrite_rule_hint_t2.b = rewrite_rule_hint_t1.b)
                         ->  Streaming(type: REDISTRIBUTE)
                               Spawn on: All datanodes
                               ->  Seq Scan on rewrite_rule_hint_t2
                         ->  Hash
                               ->  Streaming(type: REDISTRIBUTE)
                                     Spawn on: All datanodes
                                     ->  Seq Scan on rewrite_rule_hint_t1
    (16 rows)
  24. NO_LAZY_AGG

    禁用消除子查询中的聚集运算规则。该场景同时受GUC参数rewrite_rule控制,当未使用本规则的hint且rewrite_rule值未设置为lazyagg时,规则禁用。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与LAZY_AGG为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT rewrite_rule_hint_t1.b,sum(cc) FROM (SELECT /*+NO_LAZY_AGG*/b,sum(c) AS cc FROM rewrite_rule_hint_t2 GROUP BY b) s1,rewrite_rule_hint_t1 WHERE s1.b = rewrite_rule_hint_t1.b GROUP BY rewrite_rule_hint_t1.b ORDER BY 1,2;
                                        QUERY PLAN
    ----------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Merge Sort Key: rewrite_rule_hint_t1.b, (sum(s1.cc))
       Node/s: All datanodes
       ->  Sort
             Sort Key: rewrite_rule_hint_t1.b, (sum(s1.cc))
             ->  HashAggregate
                   Group By Key: rewrite_rule_hint_t1.b
                   ->  Hash Join
                         Hash Cond: (rewrite_rule_hint_t1.b = s1.b)
                         ->  Streaming(type: REDISTRIBUTE)
                               Spawn on: All datanodes
                               ->  Seq Scan on rewrite_rule_hint_t1
                         ->  Hash
                               ->  Subquery Scan on s1
                                     ->  HashAggregate
                                           Group By Key: rewrite_rule_hint_t2.b
                                           ->  Streaming(type: REDISTRIBUTE)
                                                 Spawn on: All datanodes
                                                 ->  Seq Scan on rewrite_rule_hint_t2
    (19 rows)
  25. EXPAND_SUBQUERY

    子查询提升,将子查询提升与上层做join连接,优化查询效率。该场景下本规则hint与NO_EXPAND Hint互斥,当同时使用本规则的hint和NO_EXPAND时,该hint的优先级更高。该hint与NO_EXPAND_SUBQUERY为一组互斥的规则控制hint。

    gaussdb=# SET enable_fast_query_shipping=off;
    EXPLAIN(costs off) SELECT * FROM rewrite_rule_hint_t1,(SELECT /*+EXPAND_SUBQUERY*/ * FROM rewrite_rule_hint_t2 WHERE a > 1) tt WHERE rewrite_rule_hint_t1.a = tt.a;
                                  QUERY PLAN
    ----------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Hash Join
             Hash Cond: (rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.a)
             ->  Seq Scan on rewrite_rule_hint_t1
                   Filter: (a > 1)
             ->  Hash
                   ->  Seq Scan on rewrite_rule_hint_t2
                         Filter: (a > 1)
    (9 rows)
  26. NO_EXPAND_SUBQUERY

    禁用子查询提升。该场景的hint和NO_EXPAND Hint等效,但该hint的优先级更高。该hint与EXPAND_SUBQUERY为一组互斥的规则控制hint。

    gaussdb=# SET enable_fast_query_shipping=off;
    EXPLAIN(costs off) SELECT * FROM rewrite_rule_hint_t1,(SELECT /*+NO_EXPAND_SUBQUERY*/ * FROM rewrite_rule_hint_t2 WHERE a > 1) tt WHERE rewrite_rule_hint_t1.a = tt.a;
                                  QUERY PLAN
    ----------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Hash Join
             Hash Cond: (rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.a)
             ->  Seq Scan on rewrite_rule_hint_t1
             ->  Hash
                   ->  Seq Scan on rewrite_rule_hint_t2
                         Filter: (a > 1)
    (8 rows)
  27. PUSHDOWN_HAVING

    下推HAVING条件表达式。该hint与NO_PUSHDOWN_HAVING为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT /*+PUSHDOWN_HAVING*/ sum(a),b,c FROM rewrite_rule_hint_t1 WHERE b > 0 GROUP BY b,c HAVING sum(a) > 100 AND c > 0;
                         QUERY PLAN
    ----------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  HashAggregate
             Group By Key: b, c
             Filter: (sum(a) > 100)
             ->  Streaming(type: REDISTRIBUTE)
                   Spawn on: All datanodes
                   ->  Seq Scan on rewrite_rule_hint_t1
                         Filter: ((b > 0) AND (c > 0))
    (9 rows)
  28. NO_PUSHDOWN_HAVING

    禁止下推HAVING表达式。该hint与PUSHDOWN_HAVING为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN(costs off)SELECT /*+NO_PUSHDOWN_HAVING*/ sum(a),b,c FROM rewrite_rule_hint_t1 WHERE b > 0 GROUP BY b,c HAVING sum(a) > 100 AND c > 0;
                         QUERY PLAN
    ----------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  HashAggregate
             Group By Key: b, c
             Filter: ((sum(a) > 100) AND (c > 0))
             ->  Streaming(type: REDISTRIBUTE)
                   Spawn on: All datanodes
                   ->  Seq Scan on rewrite_rule_hint_t1
                         Filter: (b > 0)
    (9 rows)
  29. INLIST_TO_JOIN

    控制使用inlist-to-join对SQL进行改写。该场景同时受GUC参数qrw_inlist2join_optmode的控制,当未使用本规则的hint且qrw_inlist2join_optmode值设置为rule_base时,规则生效。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_INLIST_TO_JOIN为一组互斥的规则控制hint。

    gaussdb=# EXPLAIN (costs off)SELECT * FROM rewrite_rule_hint_t5 WHERE slot = '5' AND (name) IN (SELECT /*+INLIST_TO_JOIN*/ name FROM rewrite_rule_hint_t5 WHERE slot = '5'AND cid IN (5,1000,1001,1002,1003,1004,1005,1006,1007,2000,4000,10781986,10880002)LIMIT 50);
                                                                QUERY PLAN
    ----------------------------------------------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Nested Loop Semi Join
             Join Filter: ((rewrite_rule_test.rewrite_rule_hint_t5.name)::text = (rewrite_rule_test.rewrite_rule_hint_t5.name)::text)
             Skew Join Optimized by Statistic
             ->  Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN)
                   Spawn on: datanode2
                   ->  Seq Scan on rewrite_rule_hint_t5
                         Filter: (slot = 5)
             ->  Materialize
                   ->  Streaming(type: PART REDISTRIBUTE PART BROADCAST)
                         Spawn on: datanode3
                         ->  Limit
                               ->  Streaming(type: BROADCAST)
                                     Spawn on: datanode2
                                     ->  Limit
                                           ->  Hash Right Semi Join
                                                 Hash Cond: ("*VALUES*".column1 = rewrite_rule_test.rewrite_rule_hint_t5.cid)
                                                 ->  Values Scan on "*VALUES*"
                                                 ->  Hash
                                                       ->  Seq Scan on rewrite_rule_hint_t5
                                                             Filter: (slot = 5)
    (22 rows)

    INLIST_TO_JOIN[(@queryblock threshold)]:支持无参数或任意大于等于0的整数值(取值范围INT型),兼容GUC参数qrw_inlist2join_optmode取值,推荐使用默认值即可。

    参数说明:

    • threshold:可选参数,查询重写阈值,可选值范围参考取值范围部分。

    默认值:1(可选,未设置即取默认值)

    取值范围:

    • 0:cost_base
    • 1:rule_base
    • 其他任意正整数(INT型):查询重写阈值,即list内元素个数大于该阈值,进行inlist2join查询重写。
  30. NO_INLIST_TO_JOIN
    控制禁止使用inlist-to-join对SQL进行改写。该场景同时受GUC参数qrw_inlist2join_optmode的控制,当未使用本规则的hint且qrw_inlist2join_optmode值设置为disable时,规则禁用。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与INLIST_TO_JOIN为一组互斥的规则控制hint。
    gaussdb=# EXPLAIN (costs off)SELECT * FROM rewrite_rule_hint_t5 WHERE slot = '5' AND (name) IN (SELECT /*+NO_INLIST_TO_JOIN*/ name FROM rewrite_rule_hint_t5 WHERE slot = '5'AND cid IN (5,1000,1001,1002,1003,1004,1005,1006,1007,2000,4000,10781986,10880002)LIMIT 50);
                                                                                   QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Nested Loop Semi Join
             Join Filter: ((rewrite_rule_test.rewrite_rule_hint_t5.name)::text = (rewrite_rule_test.rewrite_rule_hint_t5.name)::text)
             Skew Join Optimized by Statistic
             ->  Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN)
                   Spawn on: datanode2
                   ->  Seq Scan on rewrite_rule_hint_t5
                         Filter: (slot = 5)
             ->  Materialize
                   ->  Streaming(type: PART REDISTRIBUTE PART BROADCAST)
                         Spawn on: datanode1
                         ->  Limit
                               ->  Streaming(type: BROADCAST)
                                     Spawn on: datanode2
                                     ->  Limit
                                           ->  Seq Scan on rewrite_rule_hint_t5
                                                 Filter: ((slot = 5) AND (cid = ANY ('{5,1000,1001,1002,1003,1004,1005,1006,1007,2000,4000,10781986,10880002}'::bigint[])))
    (18 rows)
  31. ROWNUM_PUSHDOWN
    允许行号下推。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值未设置为disable_rownum_pushdown时,允许下推。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与NO_ROWNUM_PUSHDOWN为一组互斥的规则控制hint。
    gaussdb=# EXPLAIN (costs off) SELECT * FROM (SELECT /*+ROWNUM_PUSHDOWN*/rownum rn, a FROM rewrite_rule_hint_t1) WHERE rn BETWEEN 5 AND 10;
                                                   QUERY PLAN
    --------------------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Subquery Scan on __unnamed_subquery__
             Filter: ((__unnamed_subquery__.rn >= 5::numeric) AND (__unnamed_subquery__.rn <= 10::numeric))
             ->  Rownum
                   StopKey: (ROWNUM <= 10::numeric)
                   ->  Streaming(type: BROADCAST)
                         Spawn on: All datanodes
                         ->  Rownum
                               StopKey: (ROWNUM <= 10::numeric)
                               ->  Seq Scan on rewrite_rule_hint_t1
    (11 rows)
  32. NO_ROWNUM_PUSHDOWN
    禁止行号下推。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且rewrite_rule值设置为disable_rownum_pushdown时,禁止下推。但当同时使用本规则的hint和GUC参数时,hint的优先级高于GUC参数。该hint与ROWNUM_PUSHDOWN为一组互斥的规则控制hint。
    gaussdb=# EXPLAIN (costs off) SELECT * FROM (SELECT /*+NO_ROWNUM_PUSHDOWN*/rownum rn, a FROM rewrite_rule_hint_t1) WHERE rn BETWEEN 5 AND 10;
                                                   QUERY PLAN
    --------------------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Subquery Scan on __unnamed_subquery__
             Filter: ((__unnamed_subquery__.rn >= 5::numeric) AND (__unnamed_subquery__.rn <= 10::numeric))
             ->  Rownum
                   ->  Streaming(type: BROADCAST)
                         Spawn on: All datanodes
                         ->  Seq Scan on rewrite_rule_hint_t1
    (8 rows)
  33. WINDOWAGG_PUSHDOWN
    允许将父查询中窗口函数的过滤条件下推到子查询中。该场景同时受GUC参数rewrite_rule的控制,当未使用本规则的hint且GUC参数rewrite_rule值设置为disable_windowagg_pushdown时,禁止将父查询中窗口函数的过滤条件下推到子查询。但当同时使用本规则的hint以及将GUC参数rewrite_rule值设置为disable_windowagg_pushdown时,hint的优先级高于GUC参数,允许将父查询中窗口函数的过滤条件下推到子查询。该hint与NO_WINDOWAGG_PUSHDOWN为一组互斥的规则控制hint。
    gaussdb=# EXPLAIN (costs off) SELECT * FROM (SELECT /*+WINDOWAGG_PUSHDOWN*/ row_number() over() rid, rewrite_rule_hint_t1.a FROM rewrite_rule_hint_t1) WHERE rid BETWEEN 5 AND 10;
                                                   QUERY PLAN
    --------------------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Subquery Scan on __unnamed_subquery__
             Filter: (__unnamed_subquery__.rid >= 5)
             ->  WindowAgg
                   row_number_filter: (row_number() OVER () <= 10)
                   ->  Streaming(type: BROADCAST)
                         Spawn on: All datanodes
                         ->  WindowAgg
                               row_number_filter: (row_number() OVER () <= 10)
                               ->  Seq Scan on rewrite_rule_hint_t1
    (11 rows)
  34. NO_WINDOWAGG_PUSHDOWN
    禁止将父查询中窗口函数的过滤条件下推到子查询中。该场景同时受GUC参数rewrite_rule的控制,当使用本规则的hint或将GUC参数rewrite_rule值设置为disable_windowagg_pushdown时,禁止将父查询中窗口函数的过滤条件下推到子查询中。该hint与WINDOWAGG_PUSHDOWN为一组互斥的规则控制hint。
    gaussdb=# EXPLAIN (costs off) SELECT * FROM (SELECT /*+NO_WINDOWAGG_PUSHDOWN*/ row_number() over() rid, rewrite_rule_hint_t1.a FROM rewrite_rule_hint_t1) WHERE rid BETWEEN 5 AND 10;
                                                   QUERY PLAN
    --------------------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Node/s: All datanodes
       ->  Subquery Scan on __unnamed_subquery__
             Filter: ((__unnamed_subquery__.rid >= 5) AND (__unnamed_subquery__.rid <= 10))
             ->  WindowAgg
                   ->  Streaming(type: BROADCAST)
                         Spawn on: All datanodes
                         ->  Seq Scan on rewrite_rule_hint_t1
    (8 rows)