From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jinhuilai@email.ncu.edu.cn
Subject: BUG #18932: PostgreSQL fails to handle EXCEPT operation when the left branch is an Empty Table
Date: Thu, 15 May 2025 13:06:35 +0000
Message-ID: <18932-3bf78b809a62a0ff@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18932
Logged by: jinhui lai
Email address: jinhuilai@email.ncu.edu.cn
PostgreSQL version: 17.5
Operating system: ubuntu 22.04
Description:
Hi, PostgreSQL Developers,
Please consider such query:
SELECT * FROM empty_table EXCEPT SELECT * FROM not_empty_table;
Obviously, the above query always returns an empty set. I think it should
return an empty set quickly. However, it waste much time. PostgreSQL fails
to optimize queries containing EXCEPT operations when the left branch is an
Empty Table. This leads to unnecessary memory consumption and query
cancellation, even though the result should be deterministically empty.
I think this is a common case in actual production scenarios. It's important
to clarify that users might not intentionally perform EXCEPT operations on
empty tables. Rather, they may be unaware that a table is empty . For
example, when data has been deleted by another user or process. If
PostgreSQL can address this performance bug, it would significantly improve
query efficiency and save users valuable time in such cases.
Thank you for your valuable time, looking forward to your reply!
Best regard,
Jinhui Lai
You can reproduce the bug as follow steps:
psql -U postgres -c "CREATE DATABASE testdb;"
pgbench -U postgres -i -s 1000 testdb
psql -U postgres
\timing on
\c testdb;
testdb=# CREATE TABLE empty_table AS SELECT * FROM pgbench_accounts LIMIT 0;
SELECT 0
Time: 3.111 ms
testdb=# SELECT * FROM empty_table EXCEPT SELECT * FROM pgbench_accounts;
aid | bid | abalance | filler
-----+-----+----------+--------
(0 rows)
Time: 16077.804 ms (00:16.078) -- expect: less time
testdb=# explain SELECT * FROM empty_table EXCEPT SELECT * FROM
pgbench_accounts;
QUERY PLAN
-------------------------------------------------------------------------------------------------
HashSetOp Except (cost=0.00..5139362.35 rows=200 width=356)
-> Append (cost=0.00..4139360.25 rows=100000210 width=356)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..14.20 rows=210
width=356)
-> Seq Scan on empty_table (cost=0.00..12.10 rows=210
width=352)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..3639345.00
rows=100000000 width=101)
-> Seq Scan on pgbench_accounts (cost=0.00..2639345.00
rows=100000000 width=97)
JIT:
Functions: 6
Options: Inlining true, Optimization true, Expressions true, Deforming
true
(9 rows)
Time: 1.674 ms
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, jinhuilai@email.ncu.edu.cn
Subject: Re: BUG #18932: PostgreSQL fails to handle EXCEPT operation when the left branch is an Empty Table
In-Reply-To: <18932-3bf78b809a62a0ff@postgresql.org>
* 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