| 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 |
## Description
PostgreSQL recognizes `WHERE FALSE` as a one-time false `Result`, but still
executes `HashSetOp` and scans the opposite input for two statically empty
set
expressions.
### Expected behaviour
The planner should replace `A INTERSECT empty` and `empty EXCEPT B` with the
equivalent empty branch query `SELECT ... FROM A WHERE FALSE`.
### Actual behaviour
```text
HashSetOp Intersect/Except
└─ Append
├─ Result (One-Time Filter: false)
└─ Seq Scan on the non-empty table
```
| Case | Original set query | Mutated query after branch removal |
Difference |
|---|---:|---:|---:|
| A INTERSECT empty | 4.166 ms | 0.276 ms | 15.12x |
| empty EXCEPT B | 4.365 ms | 0.311 ms | 14.01x |
### Execution-plan evidence and decision
Every paired form returns `COUNT(*) = 0`; timings were collected in
alternating
order over seven executions. `EXPLAIN ANALYZE` shows the avoidable
operators:
```text
Original A INTERSECT empty Original empty EXCEPT B
Aggregate Aggregate
`- HashSetOp Intersect `- HashSetOp Except
`- Append `- Append
|- Result (false) |- Result (false)
`- Seq Scan on lhs `- Seq Scan on rhs
Mutated (both cases)
Aggregate
`- Result (One-Time Filter: false) -- no base-table scan or HashSetOp
```
The 15.12x and 14.01x differences are therefore supported by both
unnecessary
sequential scans and unnecessary `HashSetOp` nodes in the original plans.
## How to repeat
```sql
DROP SCHEMA IF EXISTS pg_empty_setop CASCADE;
CREATE SCHEMA pg_empty_setop;
SET search_path TO pg_empty_setop;
CREATE TABLE lhs(id INT);
CREATE TABLE rhs(id INT);
INSERT INTO lhs SELECT n FROM generate_series(1,100000) n;
INSERT INTO rhs SELECT n FROM generate_series(1,100000) n;
VACUUM ANALYZE;
EXPLAIN (ANALYZE, COSTS OFF) SELECT COUNT(*) FROM
((SELECT id FROM lhs) INTERSECT (SELECT id FROM rhs WHERE FALSE)) s;
SELECT COUNT(*) FROM
((SELECT id FROM lhs) INTERSECT (SELECT id FROM rhs WHERE FALSE)) s;
EXPLAIN (ANALYZE, COSTS OFF) SELECT COUNT(*) FROM
((SELECT id FROM lhs WHERE FALSE) EXCEPT (SELECT id FROM rhs)) s;
SELECT COUNT(*) FROM
((SELECT id FROM lhs WHERE FALSE) EXCEPT (SELECT id FROM rhs)) s;
-- Mutated form for both original queries.
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*) FROM (SELECT id FROM lhs WHERE FALSE) s;
SELECT COUNT(*) FROM (SELECT id FROM lhs WHERE FALSE) s;
```
| 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 |