From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: jh.lai@qq.com <jh.lai@qq.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18904: INTERSECT with an impossible where should eliminate both from the query plan
Date: Sun, 27 Apr 2025 14:02:36 -0400
Message-ID: <435085.1745776956@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwYDxYLNEODZhBL=7iR-7gGfrcFCz=AUBaw2=WEYrjMbhA@mail.gmail.com>
References: <18904-c5fea7892f4d26ed@postgresql.org>
<CAKFQuwYDxYLNEODZhBL=7iR-7gGfrcFCz=AUBaw2=WEYrjMbhA@mail.gmail.com>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sunday, April 27, 2025, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>> If you have a query of the form:
>> Q1 INTERSECT Q2 ... INTERSECT Qn,
>> In such cases, you know that query Qn always returns an empty set(e.g., a
>> query with WHERE 1=2), then the entire intersection will always be empty.
>> I think that such queries should be eliminated during optimization, as they
>> will always return an empty set and should never consume execution time.
> These failure to optimize requests are not bugs and are better discussed on
> the -general list where some sense of demand can be ascertained.
Indeed. To get something like this in, you have to demonstrate that
the required developer effort and planner runtime will be repaid by
successfully optimizing a nontrivial fraction of real-world cases.
I'm not really convinced about that in most of these cases.
In this specific case, it seems like it would not take very many extra
cycles for plan_set_operations and its subroutines to notice that an
input relation is "dummy" (proven empty) and then simplify the set
operation accordingly. But it might still not be worth doing, if it
complicates that already-complicated code a lot. You'd have to be
careful about ALL vs. DISTINCT for instance.
Anyway, as David said, this is not a bug. If it's something you
really want to see happen, try writing a patch yourself.
regards, tom lane
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-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, jh.lai@qq.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18904: INTERSECT with an impossible where should eliminate both from the query plan
In-Reply-To: <435085.1745776956@sss.pgh.pa.us>
* 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