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=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)
-- Drop.
gaussdb=# DROP TABLE test_range_pt;