MergeAppend

算子说明

MergeAppend用于多个有序关系集合的追加，操作类似于Append，只是通过归并的方式对有序关系的集合进行加速运算。MergeAppend以保留排序顺序的方式对子查询结果进行组合，可用于组合表分区中已排序的行。因此，与普通Append不同，MergeAppend在执行操作之前需要确保输入的 m_plan 是有序的。通常，在MergeAppend操作之前（即执行计划的子树中）会出现排序算子 Sort。

典型场景

当分区扫描路径为Index或Index Only，且分区剪枝结果大于1，并且满足以下条件时。

所有分区索引均为有效的B-tree索引；SQL查询含Limit子句；分区扫描时，不存在带Filter的分区表查询语句。

当GUC参数sql_beta_feature = 'disable_merge_append_partition'时，不再生成MergeAppend路径。

示例

--数据准备。 
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;
CREATE TABLE
gaussdb=# INSERT INTO test_range_pt VALUES (generate_series(1,10000),generate_series(1,10000),generate_series(1,10000));
INSERT 0 10000
gaussdb=# CREATE INDEX idx_range_b ON test_range_pt(b) LOCAL;
CREATE INDEX
gaussdb=# ANALYZE test_range_pt;
ANALYZE

--执行结果。 
gaussdb=# EXPLAIN ANALYZE SELECT * FROM test_range_pt WHERE b >10 AND b < 5000 ORDER BY b LIMIT 10;
                                                                                      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=153.32..153.49 rows=10 width=12) (actual time=9.131..9.138 rows=10 loops=1)
   ->  Streaming (type: GATHER)  (cost=153.32..153.66 rows=10 width=12) (actual time=9.127..9.130 rows=10 loops=1)
         Merge Sort Key: b
         Node/s: All datanodes
         ->  Limit  (cost=152.70..152.72 rows=20 width=12) (actual time=[4.777,4.782]..[4.899,4.903], rows=20)
               ->  Sort  (cost=152.70..158.94 rows=20 width=12) (actual time=[4.774,4.776]..[4.897,4.899], rows=20)
                     Sort Key: b
                     Sort Method: top-N heapsort  Memory: 26kB ~ 26kB
                     ->  Partition Iterator  (cost=0.00..98.76 rows=4991 width=12) (actual time=[0.164,3.639]..[0.179,3.743], rows=4989)
                           Iterations: 5
                           ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..98.76 rows=4991 width=12) (actual time=[0.522,2.923]..[0.602,2.980], rows=4989)
                                 Index Cond: ((b > 10) AND (b < 5000))
                                 Selected Partitions:  1..5
 Total runtime: 10.534 ms
(14 rows)

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