PostgreSQL does not propagate empty inputs through INTERSECT and EXCEPT

From: 陈列行 <2320415112(at)qq(dot)com>
To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: PostgreSQL does not propagate empty inputs through INTERSECT and EXCEPT
Date: 2026-08-17 08:28:51
Message-ID: tencent_65E56BCD08764109E4E801A42AF1AA4C2305@qq.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

##&nbsp;Description

PostgreSQL&nbsp;recognizes&nbsp;`WHERE&nbsp;FALSE`&nbsp;as&nbsp;a&nbsp;one-time&nbsp;false&nbsp;`Result`,&nbsp;but&nbsp;still
executes&nbsp;`HashSetOp`&nbsp;and&nbsp;scans&nbsp;the&nbsp;opposite&nbsp;input&nbsp;for&nbsp;two&nbsp;statically&nbsp;empty
set
expressions.

###&nbsp;Expected&nbsp;behaviour

The&nbsp;planner&nbsp;should&nbsp;replace&nbsp;`A&nbsp;INTERSECT&nbsp;empty`&nbsp;and&nbsp;`empty&nbsp;EXCEPT&nbsp;B`&nbsp;with&nbsp;the
equivalent&nbsp;empty&nbsp;branch&nbsp;query&nbsp;`SELECT&nbsp;...&nbsp;FROM&nbsp;A&nbsp;WHERE&nbsp;FALSE`.

###&nbsp;Actual&nbsp;behaviour

```text
HashSetOp&nbsp;Intersect/Except
└─&nbsp;Append
&nbsp;&nbsp;&nbsp;├─&nbsp;Result&nbsp;(One-Time&nbsp;Filter:&nbsp;false)
&nbsp;&nbsp;&nbsp;└─&nbsp;Seq&nbsp;Scan&nbsp;on&nbsp;the&nbsp;non-empty&nbsp;table
```

|&nbsp;Case&nbsp;|&nbsp;Original&nbsp;set&nbsp;query&nbsp;|&nbsp;Mutated&nbsp;query&nbsp;after&nbsp;branch&nbsp;removal&nbsp;|
Difference&nbsp;|
|---|---:|---:|---:|
|&nbsp;A&nbsp;INTERSECT&nbsp;empty&nbsp;|&nbsp;4.166&nbsp;ms&nbsp;|&nbsp;0.276&nbsp;ms&nbsp;|&nbsp;15.12x&nbsp;|
|&nbsp;empty&nbsp;EXCEPT&nbsp;B&nbsp;|&nbsp;4.365&nbsp;ms&nbsp;|&nbsp;0.311&nbsp;ms&nbsp;|&nbsp;14.01x&nbsp;|

###&nbsp;Execution-plan&nbsp;evidence&nbsp;and&nbsp;decision

Every&nbsp;paired&nbsp;form&nbsp;returns&nbsp;`COUNT(*)&nbsp;=&nbsp;0`;&nbsp;timings&nbsp;were&nbsp;collected&nbsp;in
alternating
order&nbsp;over&nbsp;seven&nbsp;executions.&nbsp;`EXPLAIN&nbsp;ANALYZE`&nbsp;shows&nbsp;the&nbsp;avoidable
operators:

```text
Original&nbsp;A&nbsp;INTERSECT&nbsp;empty&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Original&nbsp;empty&nbsp;EXCEPT&nbsp;B
Aggregate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Aggregate
`-&nbsp;HashSetOp&nbsp;Intersect&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;`-&nbsp;HashSetOp&nbsp;Except
&nbsp;&nbsp;&nbsp;`-&nbsp;Append&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;`-&nbsp;Append
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|-&nbsp;Result&nbsp;(false)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|-&nbsp;Result&nbsp;(false)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;`-&nbsp;Seq&nbsp;Scan&nbsp;on&nbsp;lhs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;`-&nbsp;Seq&nbsp;Scan&nbsp;on&nbsp;rhs

Mutated&nbsp;(both&nbsp;cases)
Aggregate
`-&nbsp;Result&nbsp;(One-Time&nbsp;Filter:&nbsp;false)&nbsp;&nbsp;--&nbsp;no&nbsp;base-table&nbsp;scan&nbsp;or&nbsp;HashSetOp
```

The&nbsp;15.12x&nbsp;and&nbsp;14.01x&nbsp;differences&nbsp;are&nbsp;therefore&nbsp;supported&nbsp;by&nbsp;both
unnecessary
sequential&nbsp;scans&nbsp;and&nbsp;unnecessary&nbsp;`HashSetOp`&nbsp;nodes&nbsp;in&nbsp;the&nbsp;original&nbsp;plans.

##&nbsp;How&nbsp;to&nbsp;repeat

```sql
DROP&nbsp;SCHEMA&nbsp;IF&nbsp;EXISTS&nbsp;pg_empty_setop&nbsp;CASCADE;
CREATE&nbsp;SCHEMA&nbsp;pg_empty_setop;
SET&nbsp;search_path&nbsp;TO&nbsp;pg_empty_setop;
CREATE&nbsp;TABLE&nbsp;lhs(id&nbsp;INT);
CREATE&nbsp;TABLE&nbsp;rhs(id&nbsp;INT);
INSERT&nbsp;INTO&nbsp;lhs&nbsp;SELECT&nbsp;n&nbsp;FROM&nbsp;generate_series(1,100000)&nbsp;n;
INSERT&nbsp;INTO&nbsp;rhs&nbsp;SELECT&nbsp;n&nbsp;FROM&nbsp;generate_series(1,100000)&nbsp;n;
VACUUM&nbsp;ANALYZE;

EXPLAIN&nbsp;(ANALYZE,&nbsp;COSTS&nbsp;OFF)&nbsp;SELECT&nbsp;COUNT(*)&nbsp;FROM
&nbsp;((SELECT&nbsp;id&nbsp;FROM&nbsp;lhs)&nbsp;INTERSECT&nbsp;(SELECT&nbsp;id&nbsp;FROM&nbsp;rhs&nbsp;WHERE&nbsp;FALSE))&nbsp;s;
SELECT&nbsp;COUNT(*)&nbsp;FROM
&nbsp;((SELECT&nbsp;id&nbsp;FROM&nbsp;lhs)&nbsp;INTERSECT&nbsp;(SELECT&nbsp;id&nbsp;FROM&nbsp;rhs&nbsp;WHERE&nbsp;FALSE))&nbsp;s;

EXPLAIN&nbsp;(ANALYZE,&nbsp;COSTS&nbsp;OFF)&nbsp;SELECT&nbsp;COUNT(*)&nbsp;FROM
&nbsp;((SELECT&nbsp;id&nbsp;FROM&nbsp;lhs&nbsp;WHERE&nbsp;FALSE)&nbsp;EXCEPT&nbsp;(SELECT&nbsp;id&nbsp;FROM&nbsp;rhs))&nbsp;s;
SELECT&nbsp;COUNT(*)&nbsp;FROM
&nbsp;((SELECT&nbsp;id&nbsp;FROM&nbsp;lhs&nbsp;WHERE&nbsp;FALSE)&nbsp;EXCEPT&nbsp;(SELECT&nbsp;id&nbsp;FROM&nbsp;rhs))&nbsp;s;

--&nbsp;Mutated&nbsp;form&nbsp;for&nbsp;both&nbsp;original&nbsp;queries.
EXPLAIN&nbsp;(ANALYZE,&nbsp;COSTS&nbsp;OFF)
SELECT&nbsp;COUNT(*)&nbsp;FROM&nbsp;(SELECT&nbsp;id&nbsp;FROM&nbsp;lhs&nbsp;WHERE&nbsp;FALSE)&nbsp;s;
SELECT&nbsp;COUNT(*)&nbsp;FROM&nbsp;(SELECT&nbsp;id&nbsp;FROM&nbsp;lhs&nbsp;WHERE&nbsp;FALSE)&nbsp;s;
```

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Vadim Ponomarev 2026-08-17 08:29:56 Reduce SyncRepLock contention on the commit path
Previous Message Jakub Wartak 2026-08-17 08:19:50 Re: MPTCP - multiplexing many TCP connections through one socket to get better bandwidth