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 Specifying the Query Block Where the Hint Is Located. @queryblock can be omitted, indicating that the hint 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 |    ->  Aggregate  [1, InitPlan 1 (returns $0)]
  3 |       ->  Streaming (type: GATHER)
  4 |          ->  Limit
  5 |             ->  Index Only Scan using minmaxtesti on minmaxtest
  6 |    ->  Aggregate  [1, InitPlan 2 (returns $1)]
  7 |       ->  Streaming (type: GATHER)
  8 |          ->  Limit
  9 |             ->  Index Only Scan Backward using minmaxtesti on minmaxtest
(9 rows)

         Predicate Information (identified by plan id)
----------------------------------------------------------------
   5 --Index Only Scan using minmaxtesti on minmaxtest
         Index Cond: (f1 IS NOT NULL)
   9 --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 |    ->  Streaming (type: GATHER)
  3 |       ->  Aggregate
  4 |          ->  Seq Scan on minmaxtest
(4 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 |    ->  Streaming (type: GATHER)
  3 |       ->  Aggregate
  4 |          ->  Seq Scan on minmaxtest
(4 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 |    ->  Aggregate  [1, InitPlan 1 (returns $0)]
  3 |       ->  Streaming (type: GATHER)
  4 |          ->  Limit
  5 |             ->  Index Only Scan using minmaxtesti on minmaxtest
  6 |    ->  Aggregate  [1, InitPlan 2 (returns $1)]
  7 |       ->  Streaming (type: GATHER)
  8 |          ->  Limit
  9 |             ->  Index Only Scan Backward using minmaxtesti on minmaxtest
(9 rows)

The use_minmax hint takes effect.

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