Consider explicit incremental sort for Append and MergeAppend

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Consider explicit incremental sort for Append and MergeAppend
Date: 2025-05-12 09:29:49
Message-ID: CAMbWs4_V7a2enTR+T3pOY_YZ-FU8ZsFYym2swOz4jNMqmSgyuw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

For ordered Append or MergeAppend, it seems that incremental sort is
currently not considered when injecting an explicit sort into subpaths
that are not sufficiently ordered. For instance:

set enable_seqscan to off;

explain (costs off)
select hundred as x, two as y from tenk1
union all
select thousand as x, tenthous as y from tenk1
order by x, y;
QUERY PLAN
-------------------------------------------------------------------
Merge Append
Sort Key: tenk1.hundred, tenk1.two
-> Sort
Sort Key: tenk1.hundred, tenk1.two
-> Index Scan using tenk1_hundred on tenk1
-> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
(6 rows)

Similar to what we do in 828e94c9d, I think we can also consider using
explicit incremental sort for ordered Append or MergeAppend. Here is
a patch doing that. With this patch, the plan above changes to:

QUERY PLAN
-------------------------------------------------------------------
Merge Append
Sort Key: tenk1.hundred, tenk1.two
-> Incremental Sort
Sort Key: tenk1.hundred, tenk1.two
Presorted Key: tenk1.hundred
-> Index Scan using tenk1_hundred on tenk1
-> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
(7 rows)

This targets v19.

Thanks
Richard

Attachment Content-Type Size
v1-0001-Consider-explicit-incremental-sort-for-Append-and.patch application/octet-stream 15.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2025-05-12 09:40:42 Re: Small fixes needed by high-availability tools
Previous Message Xuneng Zhou 2025-05-12 09:01:33 Re: Add an option to skip loading missing publication to avoid logical replication failure