更新时间：2024-11-12 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请参见表 查询改写支持的Hint列表。
- @queryblock请参见指定Hint所处于的查询块Queryblock，可省略，表示在当前查询块生效，当不指定时，hint没有括号"()"。
查询改写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使用说明
- 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 ---------------------------------------------------------------------- Sort Sort Key: inner_subquery.value DESC InitPlan 1 (returns $0) -> Aggregate -> Seq Scan on rewrite_rule_hint_t1 -> 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 (10 rows) - 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 ---------------------------------------------------------------------------------- Sort Sort Key: (sum(rewrite_rule_test.rewrite_rule_hint_t1.b)) DESC InitPlan 1 (returns $0) -> Aggregate -> Seq Scan on rewrite_rule_hint_t1 -> 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 (9 rows) - 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 ------------------------------------------------------------------------ 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 -> Seq Scan on rewrite_rule_hint_t2 -> Materialize -> Seq Scan on rewrite_rule_hint_t3 (9 rows) - 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 ------------------------------------------------------ Seq Scan on rewrite_rule_hint_t1 Filter: ((NOT (hashed SubPlan 2)) AND (SubPlan 1)) SubPlan 2 -> Seq Scan on rewrite_rule_hint_t3 SubPlan 1 -> Materialize -> Seq Scan on rewrite_rule_hint_t2 (7 rows) - 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 ---------------------------------------------------------------------- Sort Sort Key: rewrite_rule_hint_t2.b -> Hash Left Join Hash Cond: (rewrite_rule_hint_t2.b = rewrite_rule_hint_t1.b) -> Seq Scan on rewrite_rule_hint_t2 Filter: (a < 100) -> Hash -> HashAggregate Group By Key: rewrite_rule_hint_t1.b -> Seq Scan on rewrite_rule_hint_t1 (10 rows) - 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 ------------------------------------------------------------ Sort Sort Key: rewrite_rule_hint_t2.b -> Seq Scan on rewrite_rule_hint_t2 Filter: (a < 100) SubPlan 1 -> Aggregate -> Seq Scan on rewrite_rule_hint_t1 Filter: (b = rewrite_rule_hint_t2.b) (8 rows) - 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 ---------------------------------------------------------------------------------------------- Hash Join Hash Cond: (rewrite_rule_hint_t2.a = rewrite_rule_test.rewrite_rule_hint_t1.a) Join Filter: (rewrite_rule_test.rewrite_rule_hint_t1.c < (sum(rewrite_rule_hint_t2.c))) -> HashAggregate Group By Key: rewrite_rule_hint_t2.a -> Hash Join Hash Cond: (rewrite_rule_hint_t2.a = rewrite_rule_test.rewrite_rule_hint_t1.a) -> Seq Scan on rewrite_rule_hint_t2 -> Hash -> HashAggregate Group By Key: rewrite_rule_test.rewrite_rule_hint_t1.a -> Seq Scan on rewrite_rule_hint_t1 Filter: (b = 10) -> Hash -> Seq Scan on rewrite_rule_hint_t1 Filter: (b = 10) (16 rows) - 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))) -> HashAggregate Group By Key: rewrite_rule_hint_t2.a -> Seq Scan on rewrite_rule_hint_t2 -> Hash -> Seq Scan on rewrite_rule_hint_t1 Filter: (b = 10) (9 rows) - 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 ------------------------------------------------------------------------------- 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 (10 rows) - 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 ------------------------------------------------ Seq Scan on rewrite_rule_hint_t1 Filter: (a = (SubPlan 1)) SubPlan 1 -> Seq Scan on rewrite_rule_hint_t2 Filter: (rewrite_rule_hint_t1.a = b) (5 rows) - 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 --------------------------------------------------------------------------------------------------------------------------- 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)) -> 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 -> Seq Scan on rewrite_rule_hint_t3 (15 rows) - 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) - 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 ----------------------------------------------------------------------------------------------------------- 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 (8 rows) - 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 ------------------------------------------------------ Seq Scan on rewrite_rule_hint_t1 Filter: (b = (SubPlan 1)) SubPlan 1 -> Aggregate -> Seq Scan on rewrite_rule_hint_t2 Filter: (a = rewrite_rule_hint_t1.a) (6 rows) - 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 ----------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate Group By Key: ("substring"((rewrite_rule_test.rewrite_rule_hint_customer.c_phone)::text, 1, 2)) -> Sort Sort Key: ("substring"((rewrite_rule_test.rewrite_rule_hint_customer.c_phone)::text, 1, 2)) -> Hash Right Anti Join Hash Cond: (rewrite_rule_hint_orders.o_custkey = rewrite_rule_test.rewrite_rule_hint_customer.c_custkey) -> Seq Scan on rewrite_rule_hint_orders -> Hash -> Nested Loop Join Filter: (rewrite_rule_test.rewrite_rule_hint_customer.c_acctbal > (avg(rewrite_rule_test.rewrite_rule_hint_customer.c_acctbal))) -> 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[])) (15 rows) - NO_ENABLE_SUBLINK_ENHANCED
禁用子链接提升增强。该场景同时受GUC参数rewrite_rule的控制，当未使用本规则的hint且rewrite_rule值未设置为enable_sublink_pullup_enhanced时，规则禁用。但当同时使用本规则的hint和GUC参数时，hint的优先级高于GUC参数。该hint与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 /*+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 ---------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate Group By Key: ("substring"((rewrite_rule_test.rewrite_rule_hint_customer.c_phone)::text, 1, 2)) InitPlan 1 (returns $0) -> 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[]))) -> Sort Sort Key: ("substring"((rewrite_rule_test.rewrite_rule_hint_customer.c_phone)::text, 1, 2)) -> Hash Right Anti Join Hash Cond: (rewrite_rule_hint_orders.o_custkey = rewrite_rule_test.rewrite_rule_hint_customer.c_custkey) -> Seq Scan on rewrite_rule_hint_orders -> Hash -> 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[]))) (14 rows) - 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'; SET query_dop = 10; SET enable_force_smp = on; 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: LOCAL GATHER dop: 1/10) -> Hash Right Join Hash Cond: (subquery."?column?" = rewrite_rule_hint_t1.b) -> Streaming(type: BROADCAST dop: 10/10) -> 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: LOCAL REDISTRIBUTE dop: 10/10) -> Seq Scan on rewrite_rule_hint_t2 -> Hash -> Seq Scan on rewrite_rule_hint_t1 (16 rows) - 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'; SET query_dop = 10; SET enable_force_smp = on; 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 Right Join Hash Cond: (subquery."?column?" = rewrite_rule_hint_t1.b) -> 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 -> Seq Scan on rewrite_rule_hint_t2 -> Hash -> Seq Scan on rewrite_rule_hint_t1 (13 rows) - 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 ---------------------------------------------- Nested Loop -> Seq Scan on rewrite_rule_hint_t1 -> Materialize -> Seq Scan on rewrite_rule_hint_t2 (4 rows) - 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 ----------------------------------------------------- Nested Loop -> Seq Scan on rewrite_rule_hint_t1 -> Materialize -> Sort Sort Key: rewrite_rule_hint_t2.a DESC -> Seq Scan on rewrite_rule_hint_t2 (6 rows) - 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=# EXPLAIN(costs off)SELECT /*+REMOVE_NOT_NULL*/ * FROM rewrite_rule_hint_t4 WHERE b > 10 OR a IS NOT NULL; QUERY PLAN ---------------------------------- Seq Scan on rewrite_rule_hint_t4 (1 row) - 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=# EXPLAIN(costs off)SELECT /*+NO_REMOVE_NOT_NULL*/ * FROM rewrite_rule_hint_t4 WHERE b > 10 OR a IS NOT NULL; QUERY PLAN ----------------------------------------- Seq Scan on rewrite_rule_hint_t4 Filter: ((b > 10) OR (a IS NOT NULL)) (2 rows) - 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 ----------------------------------------------------------------------------- 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) -> Seq Scan on rewrite_rule_hint_t2 -> Hash -> Seq Scan on rewrite_rule_hint_t1 (9 rows) - 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 ---------------------------------------------------------------------- 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) -> Seq Scan on rewrite_rule_hint_t1 -> Hash -> Subquery Scan on s1 -> HashAggregate Group By Key: rewrite_rule_hint_t2.b -> Seq Scan on rewrite_rule_hint_t2 (12 rows) - EXPAND_SUBQUERY
子查询提升，将子查询提升与上层做join连接，优化查询效率。该场景下本规则hint与NO_EXPAND hint互斥，当同时使用本规则的hint和NO_EXPAND时，该hint的优先级更高。该hint与NO_EXPAND_SUBQUERY为一组互斥的规则控制hint。
gaussdb=# 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 ---------------------------------------------------------------- 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) (7 rows) - NO_EXPAND_SUBQUERY
禁用子查询提升。该场景的hint和NO_EXPAND Hint等效，但该hint的优先级更高。该hint与EXPAND_SUBQUERY为一组互斥的规则控制hint。
gaussdb=# 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 ---------------------------------------------------------------- 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) (6 rows) - 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 ---------------------------------------- HashAggregate Group By Key: b, c Filter: (sum(a) > 100) -> Seq Scan on rewrite_rule_hint_t1 Filter: ((b > 0) AND (c > 0)) (5 rows) - 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 ---------------------------------------- HashAggregate Group By Key: b, c Filter: ((sum(a) > 100) AND (c > 0)) -> Seq Scan on rewrite_rule_hint_t1 Filter: (b > 0) (5 rows) - 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 ---------------------------------------------------------------------------------------------------------------------------- Nested Loop Semi Join Join Filter: ((rewrite_rule_test.rewrite_rule_hint_t5.name)::text = (rewrite_rule_test.rewrite_rule_hint_t5.name)::text) -> Seq Scan on rewrite_rule_hint_t5 Filter: (slot = 5) -> 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) (11 rows)
INLIST_TO_JOIN[(@queryblock threshold)]：支持无参数或任意大于等于0的整数值（取值范围INT型），兼容GUC参数qrw_inlist2join_optmode取值，推荐使用默认值即可。
参数说明：
- threshold：可选参数，查询重写阈值，可选值范围参考取值范围部分。
默认值：1（可选，未设置即取默认值）
取值范围：
- 0：cost_base
- 1：rule_base
- 其他任意正整数（INT型）：查询重写阈值，即list内元素个数大于该阈值，进行inlist2join查询重写。
- 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 ------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop Semi Join Join Filter: ((rewrite_rule_test.rewrite_rule_hint_t5.name)::text = (rewrite_rule_test.rewrite_rule_hint_t5.name)::text) -> Seq Scan on rewrite_rule_hint_t5 Filter: (slot = 5) -> 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[]))) (7 rows) - 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 -------------------------------------------------------------------------------- Subquery Scan on __unnamed_subquery__ Filter: ((__unnamed_subquery__.rn >= 5) AND (__unnamed_subquery__.rn <= 10)) -> Rownum StopKey: (ROWNUM <= 10) -> Seq Scan on rewrite_rule_hint_t1 (5 rows) - 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 -------------------------------------------------------------------------------- Subquery Scan on __unnamed_subquery__ Filter: ((__unnamed_subquery__.rn >= 5) AND (__unnamed_subquery__.rn <= 10)) -> Rownum -> Seq Scan on rewrite_rule_hint_t1 (4 rows) - 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 --------------------------------------------------------- Subquery Scan on __unnamed_subquery__ Filter: (__unnamed_subquery__.rid >= 5) -> WindowAgg row_number_filter: (row_number() OVER () <= 10) -> Seq Scan on rewrite_rule_hint_t1 (5 rows) - 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 ---------------------------------------------------------------------------------- Subquery Scan on __unnamed_subquery__ Filter: ((__unnamed_subquery__.rid >= 5) AND (__unnamed_subquery__.rid <= 10)) -> WindowAgg -> Seq Scan on rewrite_rule_hint_t1 (4 rows) - 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 ---------------------------------------------------------------------------------------- Sort Sort Key: inner_subquery.value DESC -> Subquery Scan on inner_subquery Filter: (inner_subquery.value > inner_subquery."?column?") -> WindowAgg -> HashAggregate Group By Key: rewrite_rule_hint_t1.a -> Hash Join Hash Cond: (rewrite_rule_hint_t1.b = rewrite_rule_hint_t2.b) -> Seq Scan on rewrite_rule_hint_t1 -> Hash -> Seq Scan on rewrite_rule_hint_t2 (12 rows) - 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 ) * (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 --------------------------------------------------------------------------------------------- Sort Sort Key: (sum((public.rewrite_rule_hint_t1.c * public.rewrite_rule_hint_t1.d))) DESC InitPlan 1 (returns $0) -> Aggregate -> Hash Join Hash Cond: (public.rewrite_rule_hint_t1.b = public.rewrite_rule_hint_t2.b) -> Seq Scan on rewrite_rule_hint_t1 -> Hash -> Seq Scan on rewrite_rule_hint_t2 -> HashAggregate Group By Key: public.rewrite_rule_hint_t1.a Filter: (sum((public.rewrite_rule_hint_t1.c * public.rewrite_rule_hint_t1.d)) > $0) -> Hash Join Hash Cond: (public.rewrite_rule_hint_t1.b = public.rewrite_rule_hint_t2.b) -> Seq Scan on rewrite_rule_hint_t1 -> Hash -> Seq Scan on rewrite_rule_hint_t2 (17 rows) - 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); alter table rewrite_rule_hint_t3 add constraint fk_t3 FOREIGN KEY (b) references rewrite_rule_hint_t2(a);
执行满足查询重写条件的查询语句。
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) - 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)
父主题： 使用Plan Hint进行调优
