Help Center> GaussDB> Distributed_3.x> SQL Optimization> Hint-based Tuning> Stream Operation Hints
Updated on 2024-05-21 GMT+08:00
View PDF

Stream Operation Hints

Function

These hints specify a stream operation, which can be broadcast or redistribute. You can also directly specify a method to generate a gather plan.

Syntax

1
2
 
[no] broadcast|redistribute( [@queryblock] table_list)
gather( [@queryblock] REL|JOIN|ALL)

Parameter Description

  • 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.
  • broadcast and redistribute
    • no specifies that the specified hint will not be used for a stream operation.
    • table_list specifies the table on which a stream operation is to be performed or the tables to be joined. For details, see Parameter Description.
  • gather
    The gather hint can specify the following plan generation modes:
    • REL: Only the gather path based on the base table is generated, and then the remaining plan is executed on the CN.
    • JOIN: A join-based gather path is generated as much as possible and is added to the join subplan that can be pushed down (the join subplan does not contain the redistribution node), and the remaining plan is executed on the CN. For a join plan that requires node redistribution, such a join-based gather path cannot be generated. Instead, a base table-based gather path is generated.

      After Hint(JOIN) is specified, the plan expected by Hint(JOIN) cannot be generated if the distribution table and replication table are joined, because the optimizer has found a better plan for replacement.

    • ALL: The Gather Rel or Gather Join path is selected based on the optimal mode.

Example

Hint the query plan in Examples as follows:

1
2
 
explain
select /*+ no redistribute(store_sales store_returns item store) leading(((store_sales store_returns item store) customer)) */ i_product_name product_name ...

In the original plan, the join result of store_sales, store_returns, item, and store is redistributed before it is joined with customer. After the hinting, the redistribution is disabled and the join order is retained. The optimized plan is as follows:

Specify the gather hint for a statement.

  1. Generate the gather plan /* + GATHER(REL)*/ based on the base table.
    gaussdb=# explain select /*+ GATHER(REL)*/* from t1, t2, t3 where t1.c2 = t2.c2 and t2.c2 = t3.c2;
 id |               operation               | E-rows | E-width | E-costs 
----+---------------------------------------+--------+---------+---------
  1 | ->  Hash Join (2,8)                   |     20 |      36 | 44.10
  2 |    ->  Hash Join (3,5)                |     20 |      24 | 29.22
  3 |       ->  Streaming (type: GATHER)    |     20 |      12 | 14.35
  4 |          ->  Seq Scan on t1           |     20 |      12 | 13.13
  5 |       ->  Hash                        |     20 |      12 | 14.35
  6 |          ->  Streaming (type: GATHER) |     20 |      12 | 14.35
  7 |             ->  Seq Scan on t2        |     20 |      12 | 13.13
  8 |    ->  Hash                           |     20 |      12 | 14.35
  9 |       ->  Streaming (type: GATHER)    |     20 |      12 | 14.35
 10 |          ->  Seq Scan on t3           |     20 |      12 | 13.13
(10 rows)

 Predicate Information (identified by plan id) 
-----------------------------------------------
   1 --Hash Join (2,8)
         Hash Cond: (t1.c2 = t3.c2)
   2 --Hash Join (3,5)
         Hash Cond: (t1.c2 = t2.c2)
(4 rows)
  2. Generate the join gather plan /*+ GATHER(REL)*/ that can be pushed down.
    gaussdb=# explain select /*+ GATHER(JOIN)*/* from t1, t2, t3 where t1.c1 = t2.c1 and t2.c2 = t3.c2;
 id |             operation              | E-rows | E-width | E-costs 
----+------------------------------------+--------+---------+---------
  1 | ->  Hash Join (2,7)                |     20 |      36 | 42.37
  2 |    ->  Streaming (type: GATHER)    |     20 |      24 | 27.49
  3 |       ->  Hash Join (4,5)          |     20 |      24 | 26.56
  4 |          ->  Seq Scan on t1        |     20 |      12 | 13.13
  5 |          ->  Hash                  |     21 |      12 | 13.13
  6 |             ->  Seq Scan on t2     |     20 |      12 | 13.13
  7 |    ->  Hash                        |     20 |      12 | 14.35
  8 |       ->  Streaming (type: GATHER) |     20 |      12 | 14.35
  9 |          ->  Seq Scan on t3        |     20 |      12 | 13.13
(9 rows)

 Predicate Information (identified by plan id) 
-----------------------------------------------
   1 --Hash Join (2,7)
         Hash Cond: (t2.c2 = t3.c2)
   3 --Hash Join (4,5)
         Hash Cond: (t1.c1 = t2.c1)
(4 rows)
    3. 
  
  3. Generate the gather plan /*+ GATHER(ALL)*/ based on the optimal mode.
   
    
    The GATHER(REL) or GATHER(JOIN) path is selected based on the optimal mode and rules.
    
    gaussdb=# explain select /*+ GATHER(ALL)*/* from t1, t2, t3 where t1.c1 = t2.c1 and t2.c2 = t3.c2;
 id |             operation              | E-rows | E-width | E-costs 
----+------------------------------------+--------+---------+---------
  1 | ->  Hash Join (2,7)                |     20 |      36 | 42.37
  2 |    ->  Streaming (type: GATHER)    |     20 |      24 | 27.49
  3 |       ->  Hash Join (4,5)          |     20 |      24 | 26.56
  4 |          ->  Seq Scan on t1        |     20 |      12 | 13.13
  5 |          ->  Hash                  |     21 |      12 | 13.13
  6 |             ->  Seq Scan on t2     |     20 |      12 | 13.13
  7 |    ->  Hash                        |     20 |      12 | 14.35
  8 |       ->  Streaming (type: GATHER) |     20 |      12 | 14.35
  9 |          ->  Seq Scan on t3        |     20 |      12 | 13.13
(9 rows)

 Predicate Information (identified by plan id) 
-----------------------------------------------
   1 --Hash Join (2,7)
         Hash Cond: (t2.c2 = t3.c2)
   3 --Hash Join (4,5)
         Hash Cond: (t1.c1 = t2.c1)
(4 rows)
    
    
    
   
    4. 
 





 

  

   Parent topic: Hint-based Tuning