Help Center> GaussDB> Distributed_2.x> Performance Tuning> SQL Optimization> Hint-based Tuning> Hint of Parameterized Paths at the Same Level
Updated on 2023-10-23 GMT+08:00
View PDF

Hint of Parameterized Paths at the Same Level

Function

The predpush_same_level hint is used to specify the generation of parameterized paths between tables or materialized views at the same level.

For details about cross-layer parameterized path hints, see Parameterized Path Hint.

Syntax

1
2
 
predpush_same_level(src, dest)
predpush_same_level(src1 src2 ..., dest)

This parameter takes effect only when the predpushforce option in rewrite_rule is enabled.

Examples

Prepare parameters, tables, and indexes.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
openGauss=# set rewrite_rule = 'predpushforce';
SET
openGauss=# create table t1(a int, b int) distribute by hash(a);
CREATE TABLE
openGauss=# create table t2(a int, b int) distribute by hash(a);
CREATE TABLE
openGauss=# create index idx1 on t1(a);
CREATE INDEX
openGauss=# create index idx2 on t2(a);
CREATE INDEX

Run the following statement to view the plan:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
openGauss=# explain select * from t1, t2 where t1.a = t2.a;
                              QUERY PLAN
-----------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=18.25..77.00 rows=1000 width=16)
   Node/s: All datanodes
   ->  Hash Join  (cost=14.25..30.12 rows=1000 width=16)
         Hash Cond: (t1.a = t2.a)
         ->  Seq Scan on t1  (cost=0.00..9.00 rows=1000 width=8)
         ->  Hash  (cost=8.00..8.00 rows=1000 width=8)
               ->  Seq Scan on t2  (cost=0.00..8.00 rows=1000 width=8)
(7 rows)

The filter condition t1.a = t2.a is displayed on Join. In this case, predpush_same_level(t1, t2) can be used to push the condition down to the scan operator of t2.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
openGauss=# explain select /*+predpush_same_level(t1, t2)*/ * from t1, t2 where t1.a = t2.a;
                                QUERY PLAN
---------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=4.00..263.88 rows=1000 width=16)
   Node/s: All datanodes
   ->  Nested Loop  (cost=0.00..217.00 rows=1000 width=16)
         ->  Seq Scan on t1  (cost=0.00..9.00 rows=1000 width=8)
         ->  Index Scan using idx2 on t2  (cost=0.00..0.41 rows=1 width=8)
               Index Cond: (a = t1.a)
(6 rows)
  • predpush_same_level can specify multiple src parameters in the same condition.
  • If the specified src and dest conditions do not exist or do not meet the parameterized path requirements, this hint does not take effect.
  • If a stream operator exists on the dest scanning operator, this hint does not take effect.
Parent topic: Hint-based Tuning