MergeAppend

Description

MergeAppend is used to append multiple ordered relationship sets. The operation is similar to Append. The only difference is that MergeAppend accelerates the calculation of ordered relationship sets. MergeAppend merges subquery results in a way that preserves the sort order. It can be used to merge sorted rows in a table partition. Therefore, different from common Append, MergeAppend needs to ensure that the input m_plan is ordered before performing operations. Generally, a sort operator appears before the MergeAppend operation (that is, in the subtree of the execution plan).

Typical Scenarios

The partition scan path is index or index-only, the partition pruning result is greater than 1, and the following conditions are met:

All partition indexes are valid B-tree indexes. The SQL query contains the LIMIT clause. During partition scan, no partitioned table query statement with the filter exists.

The MergeAppend path is no longer generated when the GUC parameter sql_beta_feature is set to 'disable_merge_append_partition'.

Examples

-- Prepare data.
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

-- Execution result.
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=1.089..1.128 rows=10 loops=1)
   ->  Result  (cost=0.06..480.32 rows=10 width=12) (actual time=1.076..1.113 rows=10 loops=1)
         ->  Merge Append  (cost=0.06..480.32 rows=10 width=12) (actual time=1.073..1.106 rows=10 loops=1)
               Sort Key: b
               ->  Partition Iterator  (cost=0.00..44.61 rows=998 width=12) (actual time=0.300..0.323 rows=10 loops=1)
                     Iterations: 1
                     ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..44.61 rows=998 width=12) (actual time=0.261..0.280 rows=10 loops=1)
                           Index Cond: ((b > 10) AND (b < 5000))
                           Selected Partitions:  1
               ->  Partition Iterator  (cost=0.00..44.61 rows=998 width=12) (actual time=0.220..0.220 rows=1 loops=1)
                     Iterations: 1
                     ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..44.61 rows=998 width=12) (actual time=0.186..0.186 rows=1 loops=1)
                           Index Cond: ((b > 10) AND (b < 5000))
                           Selected Partitions:  2
               ->  Partition Iterator  (cost=0.00..44.61 rows=998 width=12) (actual time=0.212..0.212 rows=1 loops=1)
                     Iterations: 1
                     ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..44.61 rows=998 width=12) (actual time=0.183..0.183 rows=1 loops=1)
                           Index Cond: ((b > 10) AND (b < 5000))
                           Selected Partitions:  3
               ->  Partition Iterator  (cost=0.00..44.61 rows=998 width=12) (actual time=0.212..0.212 rows=1 loops=1)
                     Iterations: 1
                     ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..44.61 rows=998 width=12) (actual time=0.183..0.183 rows=1 loops=1)
                           Index Cond: ((b > 10) AND (b < 5000))
                           Selected Partitions:  4
               ->  Partition Iterator  (cost=0.00..44.61 rows=998 width=12) (actual time=0.117..0.117 rows=0 loops=1)
                     Iterations: 1
                     ->  Partitioned Index Scan using idx_range_b on test_range_pt  (cost=0.00..44.61 rows=998 width=12) (actual time=0.089..0.089 rows=0 loops=1)
                           Index Cond: ((b > 10) AND (b < 5000))
                           Selected Partitions:  5
 Total runtime: 2.585 ms
(30 rows) 

-- Drop.
gaussdb=# DROP TABLE test_range_pt;