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=0.06..1.02 rows=10 width=12) (actual time=0.727..0.744 rows=10 loops=1)
   ->  Result  (cost=0.06..480.32 rows=10 width=12) (actual time=0.724..0.740 rows=10 loops=1)
         ->  Merge Append  (cost=0.06..480.32 rows=10 width=12) (actual time=0.722..0.733 rows=10 loops=1)
               Sort Key: b
               ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..44.61 rows=998 width=12) (actual time=0.210..0.218 rows=10 loops=1)
                     Index Cond: ((b > 10) AND (b < 5000))
                     Selected Partitions:  1
               ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..44.61 rows=998 width=12) (actual time=0.144..0.144 rows=1 loops=1)
                     Index Cond: ((b > 10) AND (b < 5000))
                     Selected Partitions:  2
               ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..44.61 rows=998 width=12) (actual time=0.135..0.135 rows=1 loops=1)
                     Index Cond: ((b > 10) AND (b < 5000))
                     Selected Partitions:  3
               ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..44.61 rows=998 width=12) (actual time=0.144..0.144 rows=1 loops=1)
                     Index Cond: ((b > 10) AND (b < 5000))
                     Selected Partitions:  4
               ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..44.61 rows=998 width=12) (actual time=0.081..0.081 rows=0 loops=1)
                     Index Cond: ((b > 10) AND (b < 5000))
                     Selected Partitions:  5
 Total runtime: 1.136 ms
(20 rows)
--删除。
gaussdb=# DROP TABLE test_range_pt;