Thread

1. BUG #18906: EXCEPT with identical left/right queries isn't eliminated

   PG Bug reporting form <noreply@postgresql.org> — 2025-04-27T17:05:54Z

   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)
   
   

2. Re: BUG #18906: EXCEPT with identical left/right queries isn't eliminated

   David G. Johnston <david.g.johnston@gmail.com> — 2025-04-27T17:37:03Z

   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.