# 查询改写的Hint #### 功能描述 优化器支持一系列查询改写规则，可以对SQL语句进行等价的逻辑改写，从而生成更好的执行计划。但在一些场景下，用户并不希望改写SQL语句、或者优化器的改写会导致计划跳变，对于这些特定的场景，需要能够使用hint对改写规则进行控制，让优化器按照特定的方式进行改写。目前数据库支持对ANY/EXISTS的子链接、简单子查询、消减ORDER BY、HAVING子句下推、延迟聚合等多种场景的SQL进行hint控制，具体请参见：[Hint使用说明]。 ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/notice_3.0-zh-cn.png) - 部分查询改写规则同时受查询改写的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](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-0273.html#ZH-CN_TOPIC_0000002559105637)，可省略，表示在当前查询块生效，当不指定时，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 | 禁止父查询中窗口函数的过滤条件下推到子查询。 | | 35 | CSE_REWRITE_OPT | 允许使用公共子表达式CSE查询重写将having公共子查询重写为窗口聚集函数。 | | 36 | NO_CSE_REWRITE_OPT | 禁止使用公共子表达式CSE查询重写将having公共子查询重写为窗口聚集函数。 | | 37 | GROUPBY_PUSHDOWN_SUBQUERY | 允许使用groupby-pushdown-subquery查询重写将groupby聚集函数下推到子查询。 | | 38 | NO_GROUPBY_PUSHDOWN_SUBQUERY | 禁止使用groupby-pushdown-subquery查询重写将groupby聚集函数下推到子查询。 | #### 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。 该调优方案具体生效规则： - 主查询中包含limitoffset、limit、rownum或窗口函数时，该调优方案不生效，子查询包含limitoffset、limit、rownum或distinct on时，该调优方案不生效。 - 有JOIN条件时，当前层该调优方案可生效。 - FROM只有一个查询表时，根据上层是否生效决定当前层是否生效；有多个查询表，当前层该调优方案可生效。 - 有聚合函数时，且聚合条件不依赖结果顺序，当前层该调优方案可生效。在集中式有UNION ALL时，根据上层是否生效决定当前层是否生效。 - 当前层不是SELECT，该调优方案可生效。 - 没有可生效规则时，该调优方案不生效。 ``` 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。具体生效规则同[19.REDUCE_ORDER_BY]。 ``` 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) ``` ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) 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) ``` 35. CSE_REWRITE_OPT 允许将having公共子查询，即子查询表数量和连接方式与父查询相同的查询，进行提升成WindowAgg。目前仅支持包含一个having子查询。该hint与NO_CSE_REWRITE_OPT为一组互斥的规则控制hint。 ``` gaussdb=# EXPLAIN(costs off) SELECT /*+ CSE_REWRITE_OPT */ rewrite_rule_hint_t1.a, sum(rewrite_rule_hint_t1.c * rewrite_rule_hint_t1.d) AS VALUE FROM rewrite_rule_hint_t1,rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t1.b = rewrite_rule_hint_t2.b GROUP BY rewrite_rule_hint_t1.a HAVING sum(rewrite_rule_hint_t1.c * rewrite_rule_hint_t1.d) > (SELECT sum(rewrite_rule_hint_t1.c * rewrite_rule_hint_t1.d ) * (0.001 / 500) FROM rewrite_rule_hint_t1,rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t1.b = rewrite_rule_hint_t2.b) ORDER BY VALUE DESC; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Streaming (type: GATHER) Node/s: All datanodes -> Sort Sort Key: inner_subquery.value DESC -> Subquery Scan on inner_subquery Filter: ((inner_subquery.value)::numeric > inner_subquery."?column?") -> WindowAgg -> Streaming(type: BROADCAST) Spawn on: All datanodes -> HashAggregate Group By Key: rewrite_rule_hint_t1.a -> Streaming(type: REDISTRIBUTE) Spawn on: All datanodes -> Hash Join Hash Cond: (rewrite_rule_hint_t1.b = rewrite_rule_hint_t2.b) -> Streaming(type: REDISTRIBUTE) Spawn on: All datanodes -> Seq Scan on rewrite_rule_hint_t1 -> Hash -> Streaming(type: REDISTRIBUTE) Spawn on: All datanodes -> Seq Scan on rewrite_rule_hint_t2 (22 rows) ``` 36. NO_CSE_REWRITE_OPT 禁止将having公共子查询，即子查询表数量和连接方式与父查询相同的查询，进行提升成WindowAgg。该hint与CSE_REWRITE_OPT为一组互斥的规则控制hint。 ``` gaussdb=# explain(costs off) SELECT /*+ NO_CSE_REWRITE_OPT */ rewrite_rule_hint_t1.a, sum(rewrite_rule_hint_t1.c * rewrite_rule_hint_t1.d) AS VALUE FROM rewrite_rule_hint_t1,rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t1.b = rewrite_rule_hint_t2.b GROUP BY rewrite_rule_hint_t1.a HAVING sum(rewrite_rule_hint_t1.c * rewrite_rule_hint_t1.d) > (SELECT sum(rewrite_rule_hint_t1.c * rewrite_rule_hint_t1.d ) FROM rewrite_rule_hint_t1,rewrite_rule_hint_t2 WHERE rewrite_rule_hint_t1.b = rewrite_rule_hint_t2.b) ORDER BY VALUE DESC; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Streaming (type: GATHER) Merge Sort Key: (sum((public.rewrite_rule_hint_t1.c * public.rewrite_rule_hint_t1.d))) DESC Node/s: All datanodes -> Sort Sort Key: (sum((public.rewrite_rule_hint_t1.c * public.rewrite_rule_hint_t1.d))) DESC -> Nested Loop Join Filter: ((sum((public.rewrite_rule_hint_t1.c * public.rewrite_rule_hint_t1.d))) > (pg_catalog.sum((sum((public.rewrite_rule_hint_t1.c * public.rewrite_rule_hint_t1.d)))))) -> Aggregate -> Streaming(type: BROADCAST) Spawn on: All datanodes -> Aggregate -> Hash Join Hash Cond: (public.rewrite_rule_hint_t1.b = public.rewrite_rule_hint_t2.b) -> Streaming(type: REDISTRIBUTE) Spawn on: All datanodes -> Seq Scan on rewrite_rule_hint_t1 -> Hash -> Streaming(type: REDISTRIBUTE) Spawn on: All datanodes -> Seq Scan on rewrite_rule_hint_t2 -> Materialize -> HashAggregate Group By Key: public.rewrite_rule_hint_t1.a -> Streaming(type: REDISTRIBUTE) Spawn on: All datanodes -> Hash Join Hash Cond: (public.rewrite_rule_hint_t1.b = public.rewrite_rule_hint_t2.b) -> Streaming(type: REDISTRIBUTE) Spawn on: All datanodes -> Seq Scan on rewrite_rule_hint_t1 -> Hash -> Streaming(type: REDISTRIBUTE) Spawn on: All datanodes -> Seq Scan on rewrite_rule_hint_t2 (34 rows) ``` 37. GROUPBY_PUSHDOWN_SUBQUERY 允许将groupby聚集函数下推到子查询。为使示例生效，需要先对示例表进行如下修改。 ``` alter table rewrite_rule_hint_t1 add constraint pk_t1 primary key(a); alter table rewrite_rule_hint_t2 add constraint pk_t2 primary key(a); alter table rewrite_rule_hint_t3 add constraint pk_t3 primary key(a); alter table rewrite_rule_hint_t4 add constraint pk_t4 primary key(a); alter table rewrite_rule_hint_t2 modify b not null; alter table rewrite_rule_hint_t3 modify b not null; alter table rewrite_rule_hint_t4 modify b not null; alter table rewrite_rule_hint_t2 add constraint fk_t2 FOREIGN KEY (b) references rewrite_rule_hint_t1(a) not enforced; alter table rewrite_rule_hint_t3 add constraint fk_t3 FOREIGN KEY (b) references rewrite_rule_hint_t2(a) not enforced; ``` 执行满足查询重写条件的查询语句。 ``` gaussdb=# explain (costs off) select /*+ groupby_pushdown_subquery */ t1.b, t2.a, t3.b + t4.b, sum(t3.a+ t4.b) from rewrite_rule_hint_t1 t1, rewrite_rule_hint_t2 t2 , rewrite_rule_hint_t3 t3, rewrite_rule_hint_t4 t4 where t1.a = t2.b and t2.a = t3.b and t3.c = t4.b and t4 gaussdb-# .c > 2 group by t1.b, t3.a, t2.a, t3.b + t4.b order by 1, 2, 3, 4; QUERY PLAN ------------------------------------------------------------------- Sort Sort Key: t1.b, t2.a, ((t3.b + t4.b)), (sum((t3.a + t4.b))) -> Hash Join Hash Cond: (t3.b = t2.a) -> HashAggregate Group By Key: t3.a, (t3.b + t4.b), t3.b -> Hash Join Hash Cond: (t3.c = t4.b) -> Seq Scan on rewrite_rule_hint_t3 t3 -> Hash -> Seq Scan on rewrite_rule_hint_t4 t4 Filter: (c > 2) -> Hash -> Hash Join Hash Cond: (t2.b = t1.a) -> Seq Scan on rewrite_rule_hint_t2 t2 -> Hash -> Seq Scan on rewrite_rule_hint_t1 t1 (18 rows) ``` 38. NO_GROUPBY_PUSHDOWN_SUBQUERY 禁止将groupby聚集函数下推到子查询。该hint与GROUPBY_PUSHDOWN_SUBQUERY为一组互斥的规则控制hint。 ``` gaussdb=# explain (costs off) select /*+ no_groupby_pushdown_subquery */ t1.b, t2.a, t3.b + t4.b, sum(t3.a+ t4.b) from rewrite_rule_hint_t1 t1, rewrite_rule_hint_t2 t2 , rewrite_rule_hint_t3 t3, rewrite_rule_hint_t4 t4 where t1.a = t2.b and t2.a = t3.b and t3.c = t4.b and t4 gaussdb-# .c > 2 group by t1.b, t3.a, t2.a, t3.b + t4.b order by 1, 2, 3, 4; QUERY PLAN ------------------------------------------------------------------------- Sort Sort Key: t1.b, t2.a, ((t3.b + t4.b)), (sum((t3.a + t4.b))) -> HashAggregate Group By Key: t1.b, t3.a, t2.a, (t3.b + t4.b) -> Hash Join Hash Cond: (t3.c = t4.b) -> Hash Join Hash Cond: (t2.b = t1.a) -> Hash Join Hash Cond: (t3.b = t2.a) -> Seq Scan on rewrite_rule_hint_t3 t3 -> Hash -> Seq Scan on rewrite_rule_hint_t2 t2 -> Hash -> Seq Scan on rewrite_rule_hint_t1 t1 -> Hash -> Seq Scan on rewrite_rule_hint_t4 t4 Filter: (c > 2) (18 rows) ```