Query Rewriting Hints
Description
The optimizer supports a series of query rewriting rules and can perform equivalent logical rewriting on SQL statements to generate a better execution plan. If you do not want to perform SQL statement rewriting or the optimizer rewriting results in plan changes, you need to use hints to control the rewriting rules so that the optimizer performs rewriting in the way you want. Currently, the database supports hint control for SQL statements in various scenarios, such as ANY/EXISTS sublinks, simple subqueries, ORDER BY removal, HAVING clause pushdown, and delayed aggregation. For details, see Hint Usage Description.
- Some query rewriting rules are controlled by both query rewriting hints and GUC parameters. Generally, query rewriting hints have a higher priority than GUC parameters. Rewriting rules controlled by GUC parameters are described in Hint Usage Description.
- Each query rewriting rule is controlled by a pair of mutually exclusive hints, for example: The subquery expansion rule is controlled by both EXPAND_SUBQUERY and NO_EXPAND_SUBQUERY. The EXPAND_SUBQUERY hint indicates that the rule can be used to rewrite SQL statements, and NO_EXPAND_SUBQUERY indicates that the rule cannot be used to rewrite SQL statements. If two mutually exclusive hints exist in the same query block, the first obtained hint prevails. For example, if /*+ EXPAND_SUBQUERY NO_EXPAND_SUBQUERY */ is obtained, the EXPAND_SUBQUERY hint takes effect.
- Duplicate hints are allowed. However, for duplicate hints, only the first one is used. For other unused hints, an unused hint warning is reported. Take /*+ EXPAND_SUBLINK EXPAND_SUBLINK */ as an example, database uses only the first EXPAND_SUBLINK hint. Therefore, the unused hint warning is still displayed.
Syntax
hintname[(@queryblock)]
Parameters
- hintname: name of the hint that controls the query rewriting rule. For details about the supported query rewriting hints, see Table 1.
- For details about @queryblock, see Hint for Specifying the Query Block Where the Hint Is Located. This parameter can be omitted, indicating that it takes effect in the current query block. If it is not specified, the hint does not have parentheses (()).
Query Rewriting Hint List
|
No. |
Hint Name |
Description |
|---|---|---|
|
1 |
EXPAND_SUBLINK_HAVING |
Sublinks in the HAVING clause can be pulled up. |
|
2 |
NO_EXPAND_SUBLINK_HAVING |
Sublinks in the HAVING clause cannot be pulled up. |
|
3 |
EXPAND_SUBLINK |
Sublinks of the ANY/EXISTS type can be pulled up. |
|
4 |
NO_EXPAND_SUBLINK |
Sublinks of the ANY/EXISTS type cannot be pulled up. |
|
5 |
EXPAND_SUBLINK_TARGET |
Sublinks in the TargetList can be pulled up. |
|
6 |
NO_EXPAND_SUBLINK_TARGET |
Sublinks in the TargetList cannot be pulled up. |
|
7 |
USE_MAGIC_SET |
When conditions are pushed down from the main query to the subquery, the join columns of the subquery are grouped and aggregated, and then the subquery is joined with the main query. This reduces repeated scanning of related sublinks and improves query efficiency. |
|
8 |
NO_USE_MAGIC_SET |
Conditions cannot be pushed down from the main query to the subquery. The subquery with an aggregate operator is joined with the main query in advance. |
|
9 |
EXPAND_SUBLINK_UNIQUE_CHECK |
Sublinks without aggregation can be pulled up on the premise that only one line is displayed for each condition. |
|
10 |
NO_EXPAND_SUBLINK_UNIQUE_CHECK |
Sublinks without aggregation cannot be pulled up. |
|
11 |
NO_SUBLINK_DISABLE_REPLICATED |
Expression sublinks can be pulled up in the fast query shipping plan or stream scenario with a replication table. |
|
12 |
SUBLINK_DISABLE_REPLICATED |
Expression sublinks cannot be pulled up in the fast query shipping plan or stream scenario with a replication table. |
|
13 |
NO_SUBLINK_DISABLE_EXPR |
Expression sublinks can be pulled up. |
|
14 |
SUBLINK_DISABLE_EXPR |
Expression sublinks cannot be pulled up. |
|
15 |
ENABLE_SUBLINK_ENHANCED |
Pulling up sublinks can be enhanced. Related or non-related sublinks such as OR expressions can be pulled up. |
|
16 |
NO_ENABLE_SUBLINK_ENHANCED |
Pulling up sublinks cannot be enhanced. Related or non-related sublinks such as OR expressions cannot be pulled up. |
|
17 |
PARTIAL_PUSH |
In the stream scenario, the gather operator can be added to listagg and arrayagg. |
|
18 |
NO_PARTIAL_PUSH |
In the stream scenario, the gather operator cannot be added to listagg and arrayagg. |
|
19 |
REDUCE_ORDER_BY |
Redundant ORDER BY can be reduced. If the outer query does not have sorting requirements on the inner query result, unnecessary ORDER BY can be reduced to improve the query efficiency. |
|
20 |
NO_REDUCE_ORDER_BY |
Unnecessary ORDER BY cannot be reduced. |
|
21 |
REMOVE_NOT_NULL |
The unnecessary NOT NULL condition can be deleted. When the column attribute is NOT NULL, the IS NOT NULL judgment in the query condition can be deleted. |
|
22 |
NO_REMOVE_NOT_NULL |
The IS NOT NULL conditions cannot be deleted. |
|
23 |
LAZY_AGG |
The subquery and outer query have the same GROUP BY condition. The two-layer aggregation operation may cause low query efficiency. The aggregation operation in the subquery can be eliminated to improve query efficiency. |
|
24 |
NO_LAZY_AGG |
The aggregation operation rules in the subquery cannot be eliminated. |
|
25 |
EXPAND_SUBQUERY |
The subquery is pulled up and joined with the upper layer to optimize the query efficiency. |
|
26 |
NO_EXPAND_SUBQUERY |
The subquery cannot be pulled up. |
|
27 |
PUSHDOWN_HAVING |
The HAVING condition expression can be pushed down. |
|
28 |
NO_PUSHDOWN_HAVING |
The HAVING expression cannot be pushed down. |
|
29 |
INLIST_TO_JOIN |
The inlist-to-join can be used to rewrite SQL statements. |
|
30 |
NO_INLIST_TO_JOIN |
The inlist-to-join cannot be used to rewrite SQL statements. |
|
31 |
ROWNUM_PUSHDOWN |
Row numbers can be pushed down. |
|
32 |
NO_ROWNUM_PUSHDOWN |
Row numbers cannot be pushed down. |
|
33 |
WINDOWAGG_PUSHDOWN |
The filter criterion of the window function in the parent query can be pushed down to the subquery. |
|
34 |
NO_WINDOWAGG_PUSHDOWN |
The filter criterion of the window function in the parent query cannot be pushed down to the subquery. |
Preparations for Using Hints
To help you understand the application scenarios of hints, this document provides all application examples of query rewriting hints. For details, see Hint Usage Description. The table creation statements and environment preparation are as follows:
- Session settings:
SET client_encoding = 'UTF8'; CREATE SCHEMA rewrite_rule_test; SET current_schema = rewrite_rule_test; SET enable_codegen= off;
- Table creation statements:
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 Usage Description
- EXPAND_SUBLINK_HAVING
Sublinks in the HAVING clause can be pulled up. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is enable_sublink_pullup_enhanced, this rule also takes effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_EXPAND_SUBLINK_HAVING are mutually exclusive.
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) - NO_EXPAND_SUBLINK_HAVING
Sublinks in the HAVING clause cannot be pulled up. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is not enable_sublink_pullup_enhanced, this rule does not take effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and EXPAND_SUBLINK_HAVING are mutually exclusive.
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) - EXPAND_SUBLINK
Sublinks can be pulled up. It can control scenarios such as non-correlated sublinks of the [Not]Any type or related sublinks of the [Not]Exists type. The hint of this rule and the NO_EXPAND hint are mutually exclusive, and this hint has a higher priority than NO_EXPAND. This hint and NO_EXPAND_SUBLINK are mutually exclusive.
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) - NO_EXPAND_SUBLINK
Sublinks cannot be pulled up. It can control scenarios such as non-correlated sublinks of the [Not]Any type or related sublinks of the [Not]Exists type. The hint of this rule is equivalent to the NO_EXPAND hint. This hint and EXPAND_SUBLINK are mutually exclusive.
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) - EXPAND_SUBLINK_TARGET
Sublinks in the TargetList can be pulled up. The hint of this rule and the NO_EXPAND hint are mutually exclusive, and this hint has a higher priority than NO_EXPAND. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is intargetlist, this rule also takes effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_EXPAND_SUBLINK_TARGET are mutually exclusive.
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) - NO_EXPAND_SUBLINK_TARGET
Sublinks in the TargetList cannot be pulled up. The hint of this rule is equivalent to the NO_EXPAND hint. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is not intargetlist, this rule does not take effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and EXPAND_SUBLINK_TARGET are mutually exclusive.
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) - USE_MAGIC_SET
When conditions are pushed down from the main query to the subquery, the join columns of the subquery are grouped and aggregated, and then the subquery is joined with the main query. This reduces repeated scanning of related sublinks and improves query efficiency. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is magicset, this rule also takes effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_USE_MAGIC_SET are mutually exclusive.
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) - NO_USE_MAGIC_SET
Conditions cannot be pushed down from the main query to the subquery. The subquery with an aggregate operator is joined with the main query in advance. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is not magicset, this rule does not take effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and USE_MAGIC_SET are mutually exclusive.
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) - EXPAND_SUBLINK_UNIQUE_CHECK
Sublinks without aggregation can be pulled up if only one line is output for each condition. Sublinks with aggregation can be automatically pulled up. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is uniquecheck, this rule also takes effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_EXPAND_SUBLINK_UNIQUE_CHECK are mutually exclusive.
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) - NO_EXPAND_SUBLINK_UNIQUE_CHECK
Sublinks without aggregation cannot be pulled up. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is not uniquecheck, this rule does not take effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and EXPAND_SUBLINK_UNIQUE_CHECK are mutually exclusive.
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) - NO_SUBLINK_DISABLE_REPLICATED
Sublinks can be pulled up in the fast query shipping plan or stream scenario with a replication table. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is not disablerep, this rule also takes effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and SUBLINK_DISABLE_REPLICATED are mutually exclusive.
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) - SUBLINK_DISABLE_REPLICATED
Sublinks cannot be pulled up in the fast query shipping plan or stream scenario with a replication table. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is disablerep, this rule does not take effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_SUBLINK_DISABLE_REPLICATED are mutually exclusive.
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
Expressions in sublinks can be pulled up. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is not disable_pullup_expr_sublink, this rule also takes effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and SUBLINK_DISABLE_EXPR are mutually exclusive.
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) - SUBLINK_DISABLE_EXPR
Expressions in sublinks cannot be pulled up. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is disable_pullup_expr_sublink, this rule does not take effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_SUBLINK_DISABLE_EXPR are mutually exclusive.
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) - ENABLE_SUBLINK_ENHANCED
The sublink pullup can be enhanced. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is enable_sublink_pullup_enhanced, this rule also takes effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_ENABLE_SUBLINK_ENHANCED are mutually exclusive.
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) - NO_ENABLE_SUBLINK_ENHANCED
The sublink pullup cannot be enhanced. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is not enable_sublink_pullup_enhanced, this rule also does not take effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and BLE_SUBLINK_ENHANCED are mutually exclusive.
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) - PARTIAL_PUSH
In the stream scenario, the gather operator can be added to listagg and arrayagg. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is partialpush, this rule also takes effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_PARTIAL_PUSH are mutually exclusive.
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) - NO_PARTIAL_PUSH
In the stream scenario, the gather operator cannot be added to listagg and arrayagg. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is not partialpush, this rule does not take effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and PARTIAL_PUSH are mutually exclusive.
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) - REDUCE_ORDER_BY
Unnecessary ORDER BY can be reduced. If the outer query does not have sorting requirements on the inner query result, unnecessary ORDER BY can be reduced to improve the query efficiency. This hint and NO_REDUCE_ORDER_BY are mutually exclusive.
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) - NO_REDUCE_ORDER_BY
Unnecessary ORDER BY cannot be reduced. This hint and REDUCE_ORDER_BY are mutually exclusive.
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) - REMOVE_NOT_NULL
Unnecessary IS NOT NULL conditions can be deleted. When the column attribute is NOT NULL, the IS NOT NULL judgment in the query condition can be deleted. This scenario is also controlled by the GUC parameter enable_constraint_optimization. If the hint of this rule is not used and the value of enable_constraint_optimization is on, this rule also takes effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_REMOVE_NOT_NULL are mutually exclusive.
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) - NO_REMOVE_NOT_NULL
Unnecessary IS NOT NULL conditions cannot be deleted. This scenario is also controlled by the GUC parameter enable_constraint_optimization. If the hint of this rule is not used and the value of enable_constraint_optimization is off, this rule does not take effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and REMOVE_NOT_NULL are mutually exclusive.
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) - LAZY_AGG
The subquery and outer query have the same GROUP BY condition. The two-layer aggregation operation may cause low query efficiency. The aggregation operation in the subquery can be eliminated to improve query efficiency. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is lazyagg, this rule also takes effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_LAZY_AGG are mutually exclusive.
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) - NO_LAZY_AGG
The aggregation operation rules in the subquery cannot be eliminated. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is not lazyagg, this rule does not take effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and LAZY_AGG are mutually exclusive.
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) - EXPAND_SUBQUERY
The subquery is pulled up and joined with the upper layer to optimize the query efficiency. The hint of this rule and the NO_EXPAND hint are mutually exclusive. When both this hint and NO_EXPAND are used, this hint has a higher priority. This hint and NO_EXPAND_SUBQUERY are mutually exclusive.
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) - NO_EXPAND_SUBQUERY
The subquery cannot be pulled up. This hint is equivalent to the NO_EXPAND hint, but this hint has a higher priority. This hint and EXPAND_SUBQUERY are mutually exclusive.
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) - PUSHDOWN_HAVING
The HAVING condition expression can be pushed down. This hint and NO_PUSHDOWN_HAVING are mutually exclusive.
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) - NO_PUSHDOWN_HAVING
The HAVING expression cannot be pushed down. This hint and PUSHDOWN_HAVING are mutually exclusive.
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) - INLIST_TO_JOIN
The inlist-to-join can be used to rewrite SQL statements. This scenario is also controlled by the GUC parameter qrw_inlist2join_optmode. If the hint of this rule is not used and the value of qrw_inlist2join_optmode is rule_base, this rule takes effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_INLIST_TO_JOIN are mutually exclusive.
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)]: supports no parameter or any integer greater than or equal to 0. It is compatible with the value of the GUC parameter qrw_inlist2join_optmode. The default value is recommended.
Parameter description:
- threshold: specifies the query rewriting threshold. This parameter is optional. For details, see the value range part.
Default value: 1 (Optional. If it is not set, the default value is used.)
Value range:
- 0: cost_base
- 1: rule_base
- Any other positive integer: specifies the query rewriting threshold. If the number of elements in the list is greater than the threshold, the inlist2join query rewriting is performed.
- NO_INLIST_TO_JOIN
The inlist-to-join cannot be used to rewrite SQL statements. This scenario is also controlled by the GUC parameter qrw_inlist2join_optmode. If the hint of this rule is not used and the value of qrw_inlist2join_optmode is disable, this rule does not take effect. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and INLIST_TO_JOIN are mutually exclusive.
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) - ROWNUM_PUSHDOWN
Row numbers can be pushed down. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is disable_rownum_pushdown, pushdown is allowed. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and NO_ROWNUM_PUSHDOWN are mutually exclusive.
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) - NO_ROWNUM_PUSHDOWN
Row numbers cannot be pushed down. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is disable_rownum_pushdown, pushdown is not allowed. However, when both the hint and GUC parameter of this rule are used, the hint precedes the GUC parameter. This hint and ROWNUM_PUSHDOWN are mutually exclusive.
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) - WINDOWAGG_PUSHDOWN
The filter criterion of the window function in the parent query can be pushed down to the subquery. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is not used and the value of rewrite_rule is disable_windowagg_pushdown, the filter criterion of the window function in the parent query cannot be pushed down to the subquery. If the hint of this rule is used and the value of rewrite_rule is disable_windowagg_pushdown, the hint precedes the GUC parameter, and the filter criterion of the window function in the parent query can be pushed down to the subquery. This hint and NO_WINDOWAGG_PUSHDOWN are mutually exclusive.
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) - NO_WINDOWAGG_PUSHDOWN
The filter criterion of the window function in the parent query cannot be pushed down to the subquery. This scenario is also controlled by the GUC parameter rewrite_rule. If the hint of this rule is used or the value of rewrite_rule is disable_windowagg_pushdown, the filter criterion of the window function in the parent query cannot be pushed down to the subquery. This hint and WINDOWAGG_PUSHDOWN are mutually exclusive.
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)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
