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.

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

Syntax

1
2
3
4

predpush_same_level([@queryblock] src, dest)
predpush_same_level([@queryblock] 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.

Parameter Description

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

• For details about @queryblock, see Hint Specifying the Query Block Where the Hint Is Located. @queryblock can be omitted, indicating that the hint 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

To view the following plan example, you need to set the following parameters:

set enable_fast_query_shipping = off;
set enable_stream_operator = on;

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                            
  -----------------------------------------------------------------
   Streaming (type: GATHER)
     Node/s: All datanodes
     ->  Nested Loop
           ->  Streaming(type: BROADCAST)
                 Spawn on: All datanodes
                 ->  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))
  (10 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               
  ----------------------------------------
   Streaming (type: GATHER)
     Node/s: All datanodes
     ->  Nested Loop
           ->  Seq Scan on t2
           ->  Index Scan using it1 on t1
                 Index Cond: (c1 = t2.c1)
  (6 rows)

2. Examples of predpush_same_level:

• Prepare parameters.

  1 2 3 4

  gaussdb=# set rewrite_rule = 'predpushforce'; SET gaussdb=# set enable_fast_query_shipping=off; SET

• View the plan.

  1 2 3 4 5 6 7 8 9 10 11 12 13

  gaussdb=# explain select * from t1, t2 where t1.c1 = t2.c1; QUERY PLAN ----------------------------------------------------------------------- Streaming (type: GATHER) (cost=16.98..34.22 rows=40 width=24) Node/s: All datanodes -> Hash Join (cost=16.36..32.66 rows=40 width=24) Hash Cond: (t1.c1 = t2.c1) -> Seq Scan on t1 (cost=0.00..16.16 rows=40 width=12) -> Hash (cost=16.16..16.16 rows=40 width=12) -> Seq Scan on t2 (cost=0.00..16.16 rows=40 width=12) (7 rows)

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

  gaussdb=# explain select /*+predpush_same_level(t1, t2)*/ * from t1, t2 where t1.c1 = t2.c1; QUERY PLAN --------------------------------------------------------------------------- Streaming (type: GATHER) (cost=0.62..70.20 rows=40 width=24) Node/s: All datanodes -> Nested Loop (cost=0.00..68.64 rows=40 width=24) -> Seq Scan on t1 (cost=0.00..16.16 rows=40 width=12) -> Index Scan using it2 on t2 (cost=0.00..3.27 rows=1 width=12) Index Cond: (c1 = t1.c1) (6 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.