Max/Min

Updated on 2024-05-20 GMT+08:00

View PDF

Scenario

When the min/max function is used for a partitioned table, the SQL engine uses PI and PartitionScan to perform a full scan on the partitioned table and then performs the Sort and Limit operations. If index scan is used to scan the partition, you can perform the Limit operation on each partition to calculate the min/max value, and then perform the Sort and Limit operations on the partitioned table. In this way, when the partitioned table is sorted, the amount of data to be sorted is the same as the number of partitions because the max/min values have been calculated for each partition, so that the sorting overhead is greatly reduced.

Example

The following is an example of executing the max/min function on a partitioned table.

gaussdb=# CREATE TABLE test_range_pt (a INT, b INT, c INT)
PARTITION BY RANGE(a)
(
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN (3000),
    PARTITION p3 VALUES LESS THAN (4000),
    PARTITION p4 VALUES LESS THAN (5000),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
)ENABLE ROW MOVEMENT;
gaussdb=# CREATE INDEX idx_range_b ON test_range_pt(b) LOCAL;
gaussdb=# INSERT INTO test_range_pt VALUES(generate_series(1,10000), generate_series(1,10000), generate_series(1,10000));

Before:

gaussdb=# explain  analyze select min(b) from test_range_pt;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=164.00..164.01 rows=1 width=8) (actual time=6.779..6.780 rows=1 loops=1)
  ->  Partition Iterator  (cost=0.00..139.00 rows=10000 width=4) (actual time=0.099..4.588 rows=10000 loops=1)
      Iterations: 5
      ->  Partitioned Seq Scan on test_range_pt  (cost=0.00..139.00 rows=10000 width=4) (actual time=0.326..3.516 rows=10000 loops=5)
          Selected Partitions:  1..5
 Total runtime: 6.942 ms
(6 rows)

After:

gaussdb=# explain  analyze select min(b) from test_range_pt;
                                                                                  QUERY PLAN                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=441.25..441.26 rows=1 width=0) (actual time=0.554..0.555 rows=1 loops=1)
   InitPlan 1 (returns $2)
     ->  Limit  (cost=441.25..441.25 rows=1 width=4) (actual time=0.547..0.547 rows=1 loops=1)
           ->  Sort  (cost=441.25..466.25 rows=1 width=4) (actual time=0.544..0.544 rows=1 loops=1)
                 Sort Key: public.test_range_pt.b
                 Sort Method: top-N heapsort  Memory: 25kB
                 ->  Partition Iterator  (cost=0.00..391.25 rows=10000 width=4) (actual time=0.135..0.502 rows=5 loops=1)
                       Iterations: 5
                       ->  Limit  (cost=0.00..0.04 rows=1 width=4) (actual time=0.322..0.322 rows=5 loops=5)
                             ->  Partitioned Index Only Scan using idx_range_b on test_range_pt  (cost=0.00..391.25 rows=1 width=4) (actual time=0.319..0.319 rows=5 loops=5)
                                   Index Cond: (b IS NOT NULL)
                                   Heap Fetches: 5
                                   Selected Partitions:  1..5
 Total runtime: 0.838 ms
(14 rows)

The time consumed after the optimization is much shorter than that before the optimization.

-- Cleanup example
gaussdb=# DROP TABLE test_range_pt;

Precautions and Constraints

The max/min function is supported only when the partition scan path is index or index only.
The max/min function is supported only when all partitioned indexes are valid and are B-tree indexes.

Parent topic: Optimizing Partition Operator Execution

Previous topic: Merge Append

Next topic: Optimizing Performance of Importing Data to Partitions