From: David Rowley <drowley@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Allow planner to use Merge Append to efficiently implement UNION
Date: Mon, 25 Mar 2024 01:32:05 +0000
Message-ID: <E1roZC0-005V2k-Vi@gemulon.postgresql.org> (raw)
Allow planner to use Merge Append to efficiently implement UNION
Until now, UNION queries have often been suboptimal as the planner has
only ever considered using an Append node and making the results unique
by either using a Hash Aggregate, or by Sorting the entire Append result
and running it through the Unique operator. Both of these methods
always require reading all rows from the union subqueries.
Here we adjust the union planner so that it can request that each subquery
produce results in target list order so that these can be Merge Appended
together and made unique with a Unique node. This can improve performance
significantly as the union child can make use of the likes of btree
indexes and/or Merge Joins to provide the top-level UNION with presorted
input. This is especially good if the top-level UNION contains a LIMIT
node that limits the output rows to a small subset of the unioned rows as
cheap startup plans can be used.
Author: David Rowley
Reviewed-by: Richard Guo, Andy Fan
Discussion: https://postgr.es/m/CAApHDvpb_63XQodmxKUF8vb9M7CxyUyT4sWvEgqeQU-GB7QFoQ@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/66c0185a3d14bbbf51d0fc9d267093ffec735231
Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out | 7 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 9 +
src/backend/optimizer/path/equivclass.c | 61 +++
src/backend/optimizer/path/pathkeys.c | 19 +
src/backend/optimizer/plan/planner.c | 85 ++-
src/backend/optimizer/prep/prepunion.c | 711 +++++++++++++++++--------
src/backend/parser/analyze.c | 3 +-
src/include/nodes/pathnodes.h | 2 +
src/include/optimizer/paths.h | 4 +
src/include/optimizer/prep.h | 2 +-
src/test/regress/expected/collate.icu.utf8.out | 2 +
src/test/regress/expected/incremental_sort.out | 13 +-
src/test/regress/expected/union.out | 32 +-
src/test/regress/sql/collate.icu.utf8.sql | 2 +
src/test/regress/sql/union.sql | 8 +-
15 files changed, 707 insertions(+), 253 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: drowley@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Allow planner to use Merge Append to efficiently implement UNION
In-Reply-To: <E1roZC0-005V2k-Vi@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox