Merge Append in PostgreSQL EXPLAIN

What it does

Append concatenates its children: all rows from the first, then all from the second. Merge Append is the sorted version. Each child returns rows in the order of the Sort Key; Merge Append looks at the next row from every child and passes on the smallest, again and again. The result is one sorted stream, without sorting everything at the end.

Because it only needs the next row from each child, it can return its first row almost at once. Under a LIMIT, each child is read only as far as needed.

When the planner picks it

• ORDER BY on a UNION ALL (often hidden inside a view) or a partitioned table, when every child can return rows in that order, usually through an index on the sort column in each child.
• Especially ORDER BY … LIMIT n.
• Since PostgreSQL 17, UNION without ALL can also use it.

You won’t always see it for partitioned tables. When a table is range-partitioned on the sort column, the partitions don’t overlap, so PostgreSQL reads them one after another in partition order with a plain Append, which is cheaper still. PostgreSQL 15 extended this to more cases (tables with a DEFAULT partition or multi-value LIST partitions, when those are pruned). Merge Append is for children whose values interleave.

Reading its numbers

Merge Append  (cost=0.86..57620.86 rows=1300000 width=22) (actual time=0.045..0.047 rows=10.00 loops=1)
  Sort Key: orders.created_at
  ->  Index Scan using orders_created_at_idx on orders  (… rows=1.00 loops=1)
  ->  Index Scan using orders_archive_created_at_idx on orders_archive  (… rows=10.00 loops=1)

• Sort Key: the order every child provides and the output keeps.
• Children’s actual rows: how far each was read. Here 1 row from orders and 10 from orders_archive: all ten oldest orders came from the archive, and one row from orders was enough to know it came later.
• The estimates (rows=1300000, high total cost) are for reading everything; under a LIMIT the actual numbers are small. As with Limit, that gap is expected.
• A Sort under a child means that child couldn’t provide the order by itself and had to be sorted in full first.

When it’s a problem

1. Children need sorting. If a branch or partition has no index on the sort column, it gets a Sort node, which reads all of that child before returning a row. With a LIMIT, that throws away most of the advantage. Create the index on every partition (an index on the partitioned parent creates one on each partition automatically), or on each table in the UNION ALL.
2. Many children. It has to read the first row of every child before returning anything, so a merge over 500 partitions starts with 500 index descents. Prune partitions with a condition on the partition key where possible.
3. The planner chose something else. Without suitable indexes, you’ll see a Sort above an Append (or a Gather Merge above a Parallel Append) instead, which reads everything. That’s the signal to add the indexes.

Example

PostgreSQL 18.6, default settings. Current orders and an archive of older ones, each indexed on created_at, behind a view:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);

-- 300,000 archived orders from 2023, with the same indexes
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);
INSERT INTO orders_archive
SELECT i, 1 + (i::bigint * 104729) % 50000, 'shipped',
       round(((i * 53) % 100000) / 100.0, 2),
       timestamptz '2023-01-01' + i * interval '1 minute'
FROM generate_series(1, 300000) AS i;
VACUUM ANALYZE orders, orders_archive;

CREATE VIEW all_orders AS
  SELECT id, customer_id, status, total, created_at FROM orders
  UNION ALL
  SELECT id, customer_id, status, total, created_at FROM orders_archive;

(Our test orders table also had a foreign key to customers; it doesn’t change these plans.)

The ten oldest orders across both:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at FROM all_orders ORDER BY created_at LIMIT 10;

Limit  (cost=0.86..1.30 rows=10 width=22) (actual time=0.046..0.049 rows=10.00 loops=1)
  Buffers: shared hit=8
  ->  Merge Append  (cost=0.86..57620.86 rows=1300000 width=22) (actual time=0.045..0.047 rows=10.00 loops=1)
        Sort Key: orders.created_at
        Buffers: shared hit=8
        ->  Index Scan using orders_created_at_idx on orders  (cost=0.42..34317.43 rows=1000000 width=22) (actual time=0.018..0.018 rows=1.00 loops=1)
              Index Searches: 1
              Buffers: shared hit=4
        ->  Index Scan using orders_archive_created_at_idx on orders_archive  (cost=0.42..10303.42 rows=300000 width=22) (actual time=0.026..0.027 rows=10.00 loops=1)
              Index Searches: 1
              Buffers: shared hit=4
Planning:
  Buffers: shared hit=159
Planning Time: 0.425 ms
Execution Time: 0.076 ms

Eight pages for the ten oldest of 1.3 million rows.

The ten largest orders, with no index on total, can’t use a Merge Append. Each branch is read in full and sorted:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at FROM all_orders ORDER BY total DESC LIMIT 10;

Limit  (cost=32178.96..32180.13 rows=10 width=22) (actual time=177.567..179.979 rows=10.00 loops=1)
  Buffers: shared hit=74 read=10834
  ->  Gather Merge  (cost=32178.96..183585.50 rows=1300001 width=22) (actual time=177.565..179.976 rows=10.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=74 read=10834
        ->  Sort  (cost=31178.94..32533.10 rows=541667 width=22) (actual time=174.223..174.225 rows=9.00 loops=3)
              Sort Key: orders.total DESC
              Sort Method: top-N heapsort  Memory: 26kB
              Buffers: shared hit=74 read=10834
              Worker 0:  Sort Method: top-N heapsort  Memory: 26kB
              Worker 1:  Sort Method: top-N heapsort  Memory: 26kB
              ->  Parallel Append  (cost=0.00..19473.71 rows=541667 width=22) (actual time=1.221..118.811 rows=433333.33 loops=3)
                    Buffers: shared read=10834
                    ->  Parallel Seq Scan on orders  (cost=0.00..12500.67 rows=416667 width=22) (actual time=0.459..60.806 rows=333333.33 loops=3)
                          Buffers: shared read=8334
                    ->  Parallel Seq Scan on orders_archive  (cost=0.00..4264.71 rows=176471 width=22) (actual time=1.288..45.007 rows=150000.00 loops=2)
                          Buffers: shared read=2500
Planning:
  Buffers: shared hit=164 read=22
Planning Time: 1.318 ms
Execution Time: 180.100 ms

180 ms and 10,908 pages. With an index on total in both tables:

CREATE INDEX orders_total_idx ON orders (total);
CREATE INDEX orders_archive_total_idx ON orders_archive (total);

Limit  (cost=0.86..1.55 rows=10 width=22) (actual time=0.135..0.330 rows=10.00 loops=1)
  Buffers: shared hit=1 read=16
  ->  Merge Append  (cost=0.86..90128.69 rows=1300000 width=22) (actual time=0.134..0.326 rows=10.00 loops=1)
        Sort Key: orders.total DESC
        Buffers: shared hit=1 read=16
        ->  Index Scan Backward using orders_total_idx on orders  (cost=0.42..59328.37 rows=1000000 width=22) (actual time=0.076..0.266 rows=10.00 loops=1)
              Index Searches: 1
              Buffers: shared read=13
        ->  Index Scan Backward using orders_archive_total_idx on orders_archive  (cost=0.42..17800.31 rows=300000 width=22) (actual time=0.056..0.056 rows=1.00 loops=1)
              Index Searches: 1
              Buffers: shared hit=1 read=3
Planning:
  Buffers: shared hit=177 read=2
Planning Time: 0.435 ms
Execution Time: 0.353 ms

17 pages and 0.35 ms. The trade-off is two more indexes to keep up to date on every insert.

For comparison, a table range-partitioned on the sort column gets a plain, ordered Append, and the later partitions are never touched (from the Append page’s events table):

Limit  (cost=1.17..1.56 rows=10 width=29) (actual time=0.008..0.010 rows=10.00 loops=1)
  Buffers: shared hit=3
  ->  Append  (cost=1.17..13138.17 rows=340000 width=29) (actual time=0.007..0.008 rows=10.00 loops=1)
        Buffers: shared hit=3
        ->  Index Scan Backward using events_2026_10_happened_at_idx on events_2026_10 events_4  (cost=0.29..2533.91 rows=75041 width=30) (actual time=0.007..0.007 rows=10.00 loops=1)
              Index Searches: 1
              Buffers: shared hit=3
        ->  Index Scan Backward using events_2026_09_happened_at_idx on events_2026_09 events_3  (cost=0.29..2915.29 rows=86400 width=30) (never executed)
              Index Searches: 0
        …

That was SELECT * FROM events ORDER BY happened_at DESC LIMIT 10.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts.