Hints for Specifying Whether to Use Minmax Optimization

Description

Specifies whether a statement is rewritten using minmax.

Syntax

[no] use_minmax[(@queryblock)]

Parameters

• no indicates that minmax is not used for query rewriting.

• 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.
• use_minmax uses minmax optimization for statement query rewriting.

Examples

-- Preparation
create table minmaxtest(f1 int);
create index minmaxtesti on minmaxtest(f1);
insert into minmaxtest values(11), (12);
set explain_perf_mode=pretty;  -- Open the explain pretty option to view a detailed plan.

-- Common scenario
gaussdb=# explain (costs off) select min(f1), max(f1) from minmaxtest;
 id |                             operation                              
----+--------------------------------------------------------------------
  1 | ->  Result
  2 |    ->  Limit  [1, InitPlan 1 (returns $0)]
  3 |       ->  Index Only Scan using minmaxtesti on minmaxtest
  4 |    ->  Limit  [1, InitPlan 2 (returns $1)]
  5 |       ->  Index Only Scan Backward using minmaxtesti on minmaxtest
(5 rows)

         Predicate Information (identified by plan id)          
----------------------------------------------------------------
   3 --Index Only Scan using minmaxtesti on minmaxtest
         Index Cond: (f1 IS NOT NULL)
   5 --Index Only Scan Backward using minmaxtesti on minmaxtest
         Index Cond: (f1 IS NOT NULL)
(4 rows)

-- Use hints to disable minmax rewriting.
gaussdb=# explain (costs off)select /*+ no use_minmax*/ min(f1), max(f1) from minmaxtest;
 id |           operation           
----+-------------------------------
  1 | ->  Aggregate
  2 |    ->  Seq Scan on minmaxtest
(2 rows)

After the no use_minmax hint is used, the SQL statement does not use minmax optimization.

-- Use minmax hints.
analyze;  -- Collect statistics.
gaussdb=# explain (costs off) select min(f1), max(f1) from minmaxtest;

 id |           operation           
----+-------------------------------
  1 | ->  Aggregate
  2 |    ->  Seq Scan on minmaxtest
(2 rows)

-- Use the use_minmax hint to select minmax rewriting.
gaussdb=# explain (costs off) select  /*+ indexonlyscan(minmaxtest) use_minmax*/ min(f1), max(f1) from minmaxtest;
 id |                             operation                              
----+--------------------------------------------------------------------
  1 | ->  Result
  2 |    ->  Limit  [1, InitPlan 1 (returns $0)]
  3 |       ->  Index Only Scan using minmaxtesti on minmaxtest
  4 |    ->  Limit  [1, InitPlan 2 (returns $1)]
  5 |       ->  Index Only Scan Backward using minmaxtesti on minmaxtest
(5 rows)

         Predicate Information (identified by plan id)          
----------------------------------------------------------------
   3 --Index Only Scan using minmaxtesti on minmaxtest
         Index Cond: (f1 IS NOT NULL)
   5 --Index Only Scan Backward using minmaxtesti on minmaxtest
         Index Cond: (f1 IS NOT NULL)
(4 rows)

The use_minmax hint takes effect.

The use_minmax optimization takes effect only when index scan is used for table scanning.