pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18906: EXCEPT with identical left/right queries isn't eliminated
2+ messages / 2 participants
[nested] [flat]
* BUG #18906: EXCEPT with identical left/right queries isn't eliminated
@ 2025-04-27 17:05 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2025-04-27 17:05 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jh.lai@qq.com
The following bug has been logged on the website:
Bug reference: 18906
Logged by: jinhui lai
Email address: jh.lai@qq.com
PostgreSQL version: 17.4
Operating system: ubuntu 22.04
Description:
Dear PostgreSQL Developer:
q1 EXCEPT q1 should always return an empty set and consume minimal execution
time. It should not be executed, but the empty set is returned quickly and
directly. However, PostgreSQL actually executed such an unnecessary query.
I think such queries should be optimized.
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
\c testdb
SELECT * FROM pgbench_accounts a CROSS JOIN pgbench_tellers WHERE a.aid
>50000
EXCEPT
SELECT * FROM pgbench_accounts a CROSS JOIN pgbench_tellers WHERE a.aid >
50000;
aid | bid | abalance | filler | tid | bid | tbalance | filler
-----+-----+----------+--------+-----+-----+----------+--------
(0 rows)
Time: 231290.916 ms (03:51.291)
2. What do i expect to see?
SELECT * FROM pgbench_accounts a CROSS JOIN pgbench_tellers WHERE a.aid
>50000
EXCEPT
SELECT * FROM pgbench_accounts a CROSS JOIN pgbench_tellers WHERE a.aid >
50000;
aid | bid | abalance | filler | tid | bid | tbalance | filler
-----+-----+----------+--------+-----+-----+----------+--------
(0 rows)
Time: 0.xxx ms (few time)
3. What do i see instead?
SELECT * FROM pgbench_accounts a CROSS JOIN pgbench_tellers WHERE a.aid
>50000
EXCEPT
SELECT * FROM pgbench_accounts a CROSS JOIN pgbench_tellers WHERE a.aid >
50000;
aid | bid | abalance | filler | tid | bid | tbalance | filler
-----+-----+----------+--------+-----+-----+----------+--------
(0 rows)
Time: 231290.916 ms (03:51.291)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18906: EXCEPT with identical left/right queries isn't eliminated
@ 2025-04-27 17:37 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2025-04-27 17:37 UTC (permalink / raw)
To: jh.lai@qq.com <jh.lai@qq.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sunday, April 27, 2025, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18906
> Logged by: jinhui lai
> Email address: jh.lai@qq.com
> PostgreSQL version: 17.4
> Operating system: ubuntu 22.04
> Description:
>
As with 18094, this is not a bug.
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-04-27 17:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-27 17:05 BUG #18906: EXCEPT with identical left/right queries isn't eliminated PG Bug reporting form <noreply@postgresql.org>
2025-04-27 17:37 ` David G. Johnston <david.g.johnston@gmail.com>
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