Hints of Parameterized Paths at the Same Level

Description

The predpush_same_level and nestloop_index hints are used to specify the generation of parameterized paths between tables or materialized views at the same level.

Syntax

1
2
3
4

predpush_same_level(src, dest)
predpush_same_level(src1 src2 ..., dest)
[no] nestloop_index([@queryblock] dest[, index_list]) -- With indexes
[no] nestloop_index([@queryblock] dest[,(src1 src2 ...)]) -- With tables

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

nestloop_index has no requirement on rewrite_rule.

Parameters

• no indicates that the parameterized path of hints is not used.

• 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.
• dest is the target table of the parameterized path, that is, the table where the indexes are located.
• src is the parameter table of the parameterized path.
• index_list is the index sequence used by the parameterized path, which consists of character strings separated by spaces.

Examples

1. Examples of nestloop_index:

• Transfer t2.c1 and t3.c2 of t2 and t3 to the t1 table for index scanning (parameterized path).

  gaussdb=# explain (costs off) select /*+nestloop_index(t1,(t2 t3)) */* from t1,t2,t3 where t1.c1 = t2.c1 and t1.c2 = t3.c2;
                          QUERY PLAN                         
  -----------------------------------------------------------
   Nested Loop
     ->  Seq Scan on t3
     ->  Nested Loop
           ->  Seq Scan on t2
           ->  Index Scan using it1 on t1
                 Index Cond: ((c1 = t2.c1) AND (c2 = t3.c2))
  (6 rows)

• Perform an index scan on it1 of the t1 table (parameterized path).

  gaussdb=# explain (costs off) select /*+NestLoop_Index(t1,it1) */* from t1,t2 where t1.c1 = t2.c1;
              QUERY PLAN            
  ----------------------------------
   Nested Loop
     ->  Seq Scan on t2
     ->  Index Scan using it1 on t1
           Index Cond: (c1 = t2.c1)
  (4 rows)

2. Examples of predpush_same_level:

• Prepare parameters.

  1 2	gaussdb=# set rewrite_rule = 'predpushforce'; SET

• View the plan.

  gaussdb=# explain select * from t1, t2 where t1.c1 = t2.c1;
                              QUERY PLAN                            
  ------------------------------------------------------------------
   
   Hash Join  (cost=53.76..301.54 rows=18915 width=24)
     Hash Cond: (t1.c1 = t2.c1)
     ->  Seq Scan on t1  (cost=0.00..29.45 rows=1945 width=12)
     ->  Hash  (cost=29.45..29.45 rows=1945 width=12)
           ->  Seq Scan on t2  (cost=0.00..29.45 rows=1945 width=12)
  (5 rows)

• The filter condition t1.c1 = t2.c1 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.

  gaussdb=# explain select /*+predpush_same_level(t1, t2)*/ * from t1, t2 where t1.c1 = t2.c1;
                                QUERY PLAN                              
  ----------------------------------------------------------------------
   
   Nested Loop  (cost=0.00..1143.20 rows=18915 width=24)
     ->  Seq Scan on t1  (cost=0.00..29.45 rows=1945 width=12)
     ->  Index Scan using it2 on t2  (cost=0.00..0.47 rows=10 width=12)
           Index Cond: (c1 = t1.c1)
  (4 rows)

• You 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.