Thread: BUG #18904: INTERSECT with an impossible where should eliminate both from the query plan

The following bug has been logged on the website:

Bug reference:      18904
Logged by:          jinhui lai
Email address:      jh.lai@qq.com
PostgreSQL version: 17.4
Operating system:   ubuntu
Description:

Dear Postgres Developer,
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.
Best regards,
1. How to repeat?
docker pull postgres:latest
docker run --name postgres -e POSTGRES_PASSWORD=1213 -d -p 5432:5432
postgres
docker exec -it postgres psql -U postgres -c "CREATE DATABASE testdb;"
docker exec -it postgres pgbench -U postgres -i -s 10 testdb
docker exec -it  postgres psql -U postgres
psql (17.4 (Debian 17.4-1.pgdg120+2))
Type "help" for help.
\timing on
SELECT * FROM pgbench_accounts CROSS JOIN pgbench_tellers WHERE 1=2;
 aid | bid | abalance | filler | tid | bid | tbalance | filler
-----+-----+----------+--------+-----+-----+----------+--------
(0 rows)
Time: 0.499 ms
SELECT * FROM pgbench_accounts CROSS JOIN pgbench_tellers
INTERSECT
SELECT * FROM pgbench_accounts CROSS JOIN pgbench_tellers WHERE 1=2;
 aid | bid | abalance | filler | tid | bid | tbalance | filler
-----+-----+----------+--------+-----+-----+----------+--------
(0 rows)
Time: 16058.769 ms (00:16.059)
EXPLAIN SELECT * FROM pgbench_accounts CROSS JOIN pgbench_tellers
INTERSECT
SELECT * FROM pgbench_accounts CROSS JOIN pgbench_tellers WHERE 1=2;
QUERY PLAN
---------------------------------------------------------------------------------------------------
 HashSetOp Intersect  (cost=0.00..4776396.28 rows=1 width=708)
   ->  Append  (cost=0.00..2776396.25 rows=100000001 width=708)
         ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.00 rows=1
width=453)
               ->  Result  (cost=0.00..0.00 rows=0 width=449)
                     One-Time Filter: false
         ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..2276396.25
rows=100000000 width=453)
               ->  Nested Loop  (cost=0.00..1276396.25 rows=100000000
width=449)
                     ->  Seq Scan on pgbench_accounts  (cost=0.00..26394.00
rows=1000000 width=97)
                     ->  Materialize  (cost=0.00..2.50 rows=100 width=352)
                           ->  Seq Scan on pgbench_tellers  (cost=0.00..2.00
rows=100 width=352)
 JIT:
   Functions: 9
   Options: Inlining true, Optimization true, Expressions true, Deforming
true
(13 rows)
2. What do i expect to see?
SELECT * FROM pgbench_accounts CROSS JOIN pgbench_tellers
INTERSECT
SELECT * FROM pgbench_accounts CROSS JOIN pgbench_tellers WHERE 1=2;
 aid | bid | abalance | filler | tid | bid | tbalance | filler
-----+-----+----------+--------+-----+-----+----------+--------
(0 rows)
Time: 0.499 ms (few time)
3. What do i see instead
SELECT * FROM pgbench_accounts CROSS JOIN pgbench_tellers
INTERSECT
SELECT * FROM pgbench_accounts CROSS JOIN pgbench_tellers WHERE 1=2;
 aid | bid | abalance | filler | tid | bid | tbalance | filler
-----+-----+----------+--------+-----+-----+----------+--------
(0 rows)
Time: 16058.769 ms (00:16.059)

The following bug has been logged on the website:

Bug reference:      18904
Logged by:          jinhui lai
Email address:      jh.lai@qq.com
PostgreSQL version: 17.4
Operating system:   ubuntu
Description:       

Dear Postgres Developer,
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.

"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

On Mon, 28 Apr 2025 at 06:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Anyway, as David said, this is not a bug.  If it's something you
> really want to see happen, try writing a patch yourself.

This is already somewhat better in v18 thanks to [1].
generate_nonunion_paths() already has some code to put the smallest
child on the left, and the recent changes in nodeSetOp.c means very
little work will be done when the left side is empty.

David

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=276279295