Max/Min

分区表Max/Min优化示例如下：

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=# INSERT INTO test_range_pt VALUES(generate_series(1,10000), generate_series(1,10000), generate_series(1,10000));

非透明多写特性下，优化前：

gaussdb=# EXPLAIN ANALYZE SELECT min(b) FROM test_range_pt;
                                                                                    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.56..4.63 rows=1 width=8) (actual time=8.654..8.654 rows=1 loops=1)
   ->  Streaming (type: GATHER)  (cost=4.56..4.63 rows=3 width=8) (actual time=7.825..8.589 rows=3 loops=1)
         Node/s: All datanodes
         ->  Aggregate  (cost=4.50..4.50 rows=3 width=8) (actual time=[4.719,4.719]..[5.108,5.108], rows=3)
               ->  Partition Iterator  (cost=0.00..4.46 rows=30 width=4) (actual time=[0.110,3.964]..[0.114,4.356], rows=10000)
                     Iterations: 5
                     ->  Partitioned Index Only Scan using idx_range_b on test_range_pt  (cost=0.00..4.46 rows=30 width=4) (actual time=[0.297,3.370]..[0.569,3.752], rows=10000)
                           Heap Fetches: 0
                           Selected Partitions:  1..5
 Total runtime: 10.287 ms
(10 rows)

非透明多写特性下，优化后：

gaussdb=# CREATE INDEX idx_range_b ON test_range_pt(b) LOCAL;
gaussdb=# EXPLAIN ANALYZE SELECT min(b) FROM test_range_pt;
                                                                                            QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.64..0.65 rows=1 width=0) (actual time=2.806..2.806 rows=1 loops=1)
   InitPlan 1 (returns $2)
     ->  Aggregate  (cost=0.63..0.64 rows=1 width=4) (actual time=2.800..2.801 rows=1 loops=1)
           ->  Streaming (type: GATHER)  (cost=0.56..0.63 rows=3 width=4) (actual time=2.585..2.786 rows=3 loops=1)
                 Node/s: All datanodes
                 ->  Limit  (cost=0.50..0.50 rows=3 width=4) (actual time=[0.323,0.323]..[0.335,0.335], rows=3)
                       ->  Sort  (cost=0.50..0.52 rows=3 width=4) (actual time=[0.322,0.322]..[0.334,0.334], rows=3)
                             Sort Key: public.test_range_pt.b
                             Sort Method: top-N heapsort  Memory: 25kB ~ 25kB
                             ->  Partition Iterator  (cost=0.00..0.46 rows=15 width=4) (actual time=[0.081,0.294]..[0.084,0.304], rows=15)
                                   Iterations: 5
                                   ->  Limit  (cost=0.00..0.46 rows=3 width=4) (actual time=[0.252,0.253]..[0.257,0.257], rows=15)
                                         ->  Partitioned Index Only Scan using idx_range_b on test_range_pt  (cost=0.00..4.50 rows=3 width=4) (actual time=[0.252,0.252]..[0.255,0.255], rows=15)
                                               Index Cond: (b IS NOT NULL)
                                               Heap Fetches: 0
                                               Selected Partitions:  1..5
 Total runtime: 4.478 ms
(17 rows)

--删除表。
gaussdb=# DROP TABLE test_range_pt;

--清理示例
gaussdb=# DROP TABLE test_range_pt;