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)
