Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ SQL Optimization/ Hint-based Optimization/ AGG Hint
Updated on 2025-08-19 GMT+08:00
View PDF

AGG Hint

Description

You can specify the AGG method when performing the AGG algorithm.

Syntax

use_hash_agg[(@queryblock)], use_sort_agg[(@queryblock)]

Parameters

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. If it is not specified, the hint does not have parentheses (()).

Example

  1. Use hash aggregation. 
    gaussdb=# explain (costs off) select c1 from t2 where c1 in( select /*+ use_hash_agg */ t1.c1 from t1,t3 where t1.c1=t3.c1 group by 1);
                      QUERY PLAN                      
------------------------------------------------------
 Streaming (type: GATHER)
   Node/s: All datanodes
   ->  Hash Join
         Hash Cond: (t2.c1 = t1.c1)
         ->  Seq Scan on t2
         ->  Hash
               ->  HashAggregate
                     Group By Key: t1.c1
                     ->  Hash Join
                           Hash Cond: (t1.c1 = t3.c1)
                           ->  Seq Scan on t1
                           ->  Hash
                                 ->  Seq Scan on t3
(13 rows)
  2. Use use_sort_agg for aggregation and then perform merge join. 
    gaussdb=# explain (costs off) select c1 from t2 where c1 in( select /*+ use_sort_agg */ t1.c1 from t1,t3 where t1.c1=t3.c1 group by 1);
                         QUERY PLAN                         
------------------------------------------------------------
 Streaming (type: GATHER)
   Node/s: All datanodes
   ->  Hash Join
         Hash Cond: (t2.c1 = t1.c1)
         ->  Seq Scan on t2
         ->  Hash
               ->  Group
                     Group By Key: t1.c1
                     ->  Sort
                           Sort Key: t1.c1
                           ->  Hash Join
                                 Hash Cond: (t1.c1 = t3.c1)
                                 ->  Seq Scan on t1
                                 ->  Hash
                                       ->  Seq Scan on t3
(15 rows)
Parent topic: Hint-based Optimization