Merge Append

场景描述

当对分区表进行全局排序时，通常SQL引擎的实现方式是先通过Partition Iterator + PartitionScan对分区表做全量扫描，然后进行Sort排序操作，这样难以利用分区局部有序性加速排序过程。针对这类问题，目前分区表支持分区归并排序执行策略，利用Merge Append的执行机制改进分区表的排序机制。

示例

非透明多写特性下，分区表Merge Append的执行机制示例如下：

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=# CREATE INDEX idx_range_b ON test_range_pt(b) LOCAL;
gaussdb=# ANALYZE test_range_pt;
gaussdb=# SET enable_stream_operator=off;
gaussdb=# SET max_datanode_for_plan=1;

gaussdb=# EXPLAIN SELECT * FROM test_range_pt  WHERE b >10 AND b < 5000 ORDER BY b LIMIT 10;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Limit  (cost=107.85..107.88 rows=10 width=12)
   ->  Sort  (cost=107.85..120.33 rows=10 width=12)
         Sort Key: test_range_pt.b
         ->  Data Node Scan on "__REMOTE_LIMIT_QUERY__"  (cost=0.00..0.00 rows=10 width=12)
               Node/s: All datanodes

 Remote SQL: SELECT a, b, c FROM ONLY public.test_range_pt WHERE b > 10 AND b < 5000 ORDER BY 2 LIMIT 10::bigint
 Datanode Name: datanode1
   Limit  (cost=0.06..1.15 rows=10 width=12)
     ->  Result  (cost=0.06..183.67 rows=10 width=12)
           ->  Merge Append  (cost=0.06..183.67 rows=10 width=12)
                 Sort Key: b
                 ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..19.36 rows=336 width=12)
                       Index Cond: ((b > 10) AND (b < 5000))
                       Selected Partitions:  1
                 ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..19.36 rows=336 width=12)
                       Index Cond: ((b > 10) AND (b < 5000))
                       Selected Partitions:  2
                 ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..19.36 rows=336 width=12)
                       Index Cond: ((b > 10) AND (b < 5000))
                       Selected Partitions:  3
                 ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..19.36 rows=336 width=12)
                       Index Cond: ((b > 10) AND (b < 5000))
                       Selected Partitions:  4
                 ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..19.36 rows=336 width=12)
                       Index Cond: ((b > 10) AND (b < 5000))
                       Selected Partitions:  5

(28 rows)

--关闭分区表Merge Append算子。
gaussdb=# SET sql_beta_feature = 'disable_merge_append_partition';
SET

gaussdb=# EXPLAIN SELECT * FROM test_range_pt WHERE b >10 AND b < 5000 ORDER BY b LIMIT 10;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=107.85..107.88 rows=10 width=12)
   ->  Sort  (cost=107.85..120.33 rows=10 width=12)
         Sort Key: test_range_pt.b
         ->  Data Node Scan on "__REMOTE_LIMIT_QUERY__"  (cost=0.00..0.00 rows=10 width=12)
               Node/s: All datanodes

 Remote SQL: SELECT a, b, c FROM ONLY public.test_range_pt WHERE b > 10 AND b < 5000 ORDER BY 2 LIMIT 10::bigint
 Datanode Name: datanode1
   Limit  (cost=102.89..102.91 rows=10 width=12)
     ->  Sort  (cost=102.89..107.08 rows=10 width=12)
           Sort Key: b
           ->  Partition Iterator  (cost=0.00..66.62 rows=1678 width=12)
                 Iterations: 5
                 ->  Partitioned Seq Scan on test_range_pt  (cost=0.00..66.62 rows=1678 width=12)
                       Filter: ((b > 10) AND (b < 5000))
                       Selected Partitions:  1..5

(17 rows)

gaussdb=# DROP TABLE test_range_pt;

Merge Append的执行代价远小于普通执行方式。