Stream Operation Hints

1
2

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

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

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. 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)

   local_roundrobin hint usage:

   SET enable_fast_query_shipping=off; -- Disable FQS optimization.
   SET query_dop=4; -- Set the degree of parallelism.
   gaussdb=# EXPLAIN(costs off) SELECT /*+ local_roundrobin(t2) scandop(t2 1) scandop(t1 4)*/* FROM t1, t2  WHERE t1.c1 = t2.c1;
    id |                         operation
   ----+------------------------------------------------------------
     1 | ->  Streaming (type: GATHER)
     2 |    ->  Streaming(type: LOCAL GATHER dop: 1/4)
     3 |       ->  Nested Loop (4,6)
     4 |          ->  Streaming(type: LOCAL BROADCAST dop: 4/4)
     5 |             ->  Seq Scan on t1
     6 |          ->  Materialize
     7 |             ->  Streaming(type: LOCAL ROUNDROBIN dop: 4/1)
     8 |                ->  Seq Scan on t2
   (8 rows)
   
    Predicate Information (identified by plan id)
   -----------------------------------------------
      3 --Nested Loop (4,6)
            Join Filter: (t1.c1 = t2.c1)
   (2 rows)

   The local_roundrobin data distribution mode is successfully selected. However, you are advised not to use this mode because there are many restrictions (such as setting the degree of parallelism and disabling FQS optimization).

   

   The local_roundrobin hint takes effect only when the degree of parallelism for table scanning is 1. You are advised to use this hint with scandop hint.