Hints for Specifying the Stream Hashagg Optimization

Description

In a parallel execution plan, if the column used by an operator is different from that used by GROUP BY and the hash operator is used, redistribution is optimized for the plan. In this case, you can use this hint to control the generation of a plan.

Syntax

redistribute_agg[(@queryblock)], agg_redistribute_agg[(@queryblock)]

Parameters

• 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.
• redistribute_agg specifies that stream hashagg uses the redistribution-aggregation plan.
• agg_redistribute_agg specifies that stream hashagg uses the aggregation-redistribution-aggregation plan.

Examples

-- Preparation
CREATE TABLE agg_t1 (a int, b int, c int);
set explain_perf_mode=pretty;  -- Open the explain pretty option to view a detailed plan.
set query_dop = 2; -- Set the degree of parallelism.

-- Do not use stream agg hints.
gaussdb=# EXPLAIN (costs off) SELECT /*+ scandop(agg_t1 2)*/ a, avg(b), sum(c)
    FROM agg_t1
    GROUP by a
    ORDER by 1,2,3;
 id |                         operation                         
----+-----------------------------------------------------------
  1 | ->  Sort
  2 |    ->  Streaming(type: LOCAL GATHER dop: 1/2)
  3 |       ->  HashAggregate
  4 |          ->  Streaming(type: LOCAL REDISTRIBUTE dop: 2/2)
  5 |             ->  HashAggregate
  6 |                ->  Seq Scan on agg_t1
(6 rows)

-- Use redistribute_agg hints.
gaussdb=# EXPLAIN (costs off) SELECT /*+ redistribute_agg scandop(agg_t1 2)*/ a, avg(b), sum(c)
    FROM agg_t1
    GROUP BY a
    ORDER BY 1,2,3;
 id |                         operation                         
----+-----------------------------------------------------------
  1 | ->  Sort
  2 |    ->  Streaming(type: LOCAL GATHER dop: 1/2)
  3 |       ->  HashAggregate
  4 |          ->  Streaming(type: LOCAL REDISTRIBUTE dop: 2/2)
  5 |             ->  Seq Scan on agg_t1
(5 rows)

You can see that redistribute_agg hints can change the plan to redistribution-aggregation instead of aggregation-redistribution-aggregation.