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>,
        "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
In-reply-to: 
 <CAKFQuwYDxYLNEODZhBL=7iR-7gGfrcFCz=AUBaw2=WEYrjMbhA@mail.gmail.com>
References: <18904-c5fea7892f4d26ed@postgresql.org>
 <CAKFQuwYDxYLNEODZhBL=7iR-7gGfrcFCz=AUBaw2=WEYrjMbhA@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Sun, 27 Apr 2025 10:32:22 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <435084.1745776956.1@sss.pgh.pa.us>
Date: Sun, 27 Apr 2025 14:02:36 -0400
Message-ID: <435085.1745776956@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/435085.1745776956%40sss.pgh.pa.us>
Precedence: bulk

"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