pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18905: The opposite WHERE clause intersects and is always an empty set. It should perform no action.
4+ messages / 4 participants
[nested] [flat]
* BUG #18905: The opposite WHERE clause intersects and is always an empty set. It should perform no action.
@ 2025-04-27 16:49 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2025-04-27 16:49 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: 18905
Logged by: jinhui lai
Email address: jh.lai@qq.com
PostgreSQL version: 17.4
Operating system: ubuntu 22.04
Description:
Dear PostgreSQL Developer:
Since q1 and q2 have opposite WHERE clause (e.g., > and <=, = and !=), q1
INTERSECT q2 should always produce empty set and without physical read.
q1: SELECT * FROM pgbench_accounts a CROSS JOIN pgbench_tellers WHERE a.aid
> 50000;
q2:SELECT * FROM pgbench_accounts a CROSS JOIN pgbench_tellers WHERE a.aid
<= 50000;
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
INTERSECT
SELECT * FROM pgbench_accounts a CROSS JOIN pgbench_tellers WHERE a.aid <=
50000;
aid | bid | abalance | filler | tid | bid | tbalance | filler
-----+-----+----------+--------+-----+-----+----------+--------
(0 rows)
Time: 94073.372 ms (01:34.073)
2. What do i expect to see?
SELECT * FROM pgbench_accounts a CROSS JOIN pgbench_tellers WHERE a.aid >
50000
INTERSECT
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
INTERSECT
SELECT * FROM pgbench_accounts a CROSS JOIN pgbench_tellers WHERE a.aid <=
50000;
aid | bid | abalance | filler | tid | bid | tbalance | filler
-----+-----+----------+--------+-----+-----+----------+--------
(0 rows)
Time: 94073.372 ms (01:34.073)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18905: The opposite WHERE clause intersects and is always an empty set. It should perform no action.
@ 2025-04-27 17:34 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2025-04-27 17:34 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: 18905
> Logged by: jinhui lai
> Email address: jh.lai@qq.com
> PostgreSQL version: 17.4
> Operating system: ubuntu 22.04
> Description:
>
As with 18904, this is not a bug.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18905: The opposite WHERE clause intersects and is always an empty set. It should perform no action.
@ 2025-04-27 17:54 =?utf-8?B?6LWW6ZSm6L6J?= <jh.lai@qq.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: 赖锦辉 @ 2025-04-27 17:54 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Dear PostgreSQL Development Team,
Thanks for your reply. But I don't believe this optimization is meaningless. Let me illustrate with two examples:
Example 1: Users inevitably make mistakes when writing SQL, such as accidentally writing WHERE a1=2 instead of WHERE 1=2.
Example 2: Some automated testing tools may randomly generate similar cases.
If PostgreSQL can optimize for such scenarios, it would save users significant time and effort. Similar issues have been found in MySQL and MariaDB, where they have been acknowledged and addressed — in fact, MariaDB has already fixed such problems.
As a loyal PostgreSQL user, I sincerely hope these issues can be addressed to make PostgreSQL even better.
Best regards,
原始邮件
发件人：David G. Johnston <david.g.johnston@gmail.com>
发件时间：2025年4月28日 01:34
收件人：jh.lai@qq.com <jh.lai@qq.com>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
主题：Re: BUG #18905: The opposite WHERE clause intersects and is always anempty set. It should perform no action.
On Sunday, April 27, 2025, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 18905
Logged by: jinhui lai
Email address: jh.lai@qq.com
PostgreSQL version: 17.4
Operating system: ubuntu 22.04
Description:
As with 18904, this is not a bug.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18905: The opposite WHERE clause intersects and is always an empty set. It should perform no action.
@ 2025-04-27 19:19 Christophe Pettus <xof@thebuild.com>
parent: =?utf-8?B?6LWW6ZSm6L6J?= <jh.lai@qq.com>
0 siblings, 0 replies; 4+ messages in thread
From: Christophe Pettus @ 2025-04-27 19:19 UTC (permalink / raw)
To: 赖锦辉 <jh.lai@qq.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
> On Apr 27, 2025, at 10:54, 赖锦辉 <jh.lai@qq.com> wrote:
> But I don't believe this optimization is meaningless.
I don't believe anyone claims this is "meaningless."
Every condition the planner has to check for adds complexity and time to the planner. If this will provide a significant improvement in plans for a reasonable number of real-life cases, then it might well be worth it. This is especially true when there is no way of writing the query without running into the issue.
However, many proposed improvements optimize for a relatively uncommon case, but would add time and complexity for all queries. In this case, it appears that this case is relatively uncommon, and it can be easily fixed by rewriting the query. Given that the project does not have infinite resources to dedicate to planner improvements, it does not seem to be an area that would reward the work put into it.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-04-27 19:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-27 16:49 BUG #18905: The opposite WHERE clause intersects and is always an empty set. It should perform no action. PG Bug reporting form <noreply@postgresql.org>
2025-04-27 17:34 ` David G. Johnston <david.g.johnston@gmail.com>
2025-04-27 17:54 ` =?utf-8?B?6LWW6ZSm6L6J?= <jh.lai@qq.com>
2025-04-27 19:19 ` Christophe Pettus <xof@thebuild.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