From: David Rowley <drowley@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Teach planner to short-circuit EXCEPT/INTERSECT with dummy input
Date: Tue, 07 Oct 2025 04:18:32 +0000
Message-ID: <E1v5z9k-000Rb4-2w@gemulon.postgresql.org> (raw)
Teach planner to short-circuit EXCEPT/INTERSECT with dummy inputs
When either inputs of an INTERSECT [ALL] operator are proven not to return
any results (a dummy rel), then mark the entire INTERSECT operation as
dummy.
Likewise, if an EXCEPT [ALL] operation's left input is proven empty, then
mark the entire operation as dummy.
With EXCEPT ALL, we can easily handle the right input being dummy as
we can return the left input without any processing. That can lead to
significant performance gains during query execution. We can't easily
handle dummy right inputs for EXCEPT (without ALL), as that would require
deduplication of the left input. Wiring up those Paths is likely more
complex than it's worth as the gains during execution aren't that great,
so let's leave that one to be handled by the normal Path generation code.
Author: David Rowley <dgrowleyml@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/CAApHDvri53PPF76c3M94_QNWbJfXjyCnjXuj_2=LYM-0m8WZtw@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/9c9d41af4db7e7f81d0f9abc7dc16402386091b0
Modified Files
--------------
src/backend/optimizer/prep/prepunion.c | 63 +++++++++++++++++++++++++
src/test/regress/expected/union.out | 86 +++++++++++++++++++++++++++++++++-
src/test/regress/sql/union.sql | 38 ++++++++++++++-
3 files changed, 185 insertions(+), 2 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: Teach planner to short-circuit EXCEPT/INTERSECT with dummy input
In-Reply-To: <E1v5z9k-000Rb4-2w@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