Thread

1. BUG #18930: PostgreSQL fails to handle INTERSECT operation involving Empty Table

   PG Bug reporting form <noreply@postgresql.org> — 2025-05-15T07:26:12Z

   The following bug has been logged on the website:
   
   Bug reference:      18930
   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 queries:
   SELECT * FROM empty_table INTERSECT SELECT * FROM not_empty_table;
   SELECT * FROM not_empty_table INTERSECT SELECT * FROM empty_table;
   Obviously, the above queries 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 INTERSECT operations when one branch includes
   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 INTERSECT 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 INTERSECT SELECT * FROM pgbench_accounts;
    aid | bid | abalance | filler
   -----+-----+----------+--------
   (0 rows)
   Time: 16236.845 ms (00:16.237) -- expect less time
   testdb=# explain SELECT * FROM empty_table INTERSECT SELECT * FROM
   pgbench_accounts;
                                              QUERY PLAN
   -------------------------------------------------------------------------------------------------
    HashSetOp Intersect  (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.446 ms
   testdb=# SELECT * FROM pgbench_accounts INTERSECT SELECT * FROM empty_table;
    aid | bid | abalance | filler
   -----+-----+----------+--------
   (0 rows)
   Time: 16303.520 ms (00:16.304)  -- expect less time
   testdb=# explain SELECT * FROM pgbench_accounts INTERSECT SELECT * FROM
   empty_table;
                                              QUERY PLAN
   -------------------------------------------------------------------------------------------------
    HashSetOp Intersect  (cost=0.00..5139362.35 rows=200 width=356)
      ->  Append  (cost=0.00..4139360.25 rows=100000210 width=356)
            ->  Subquery Scan on "*SELECT* 2"  (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* 1"  (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.732 ms
   
   

2. Re: BUG #18930: PostgreSQL fails to handle INTERSECT operation involving Empty Table

   David G. Johnston <david.g.johnston@gmail.com> — 2025-05-15T14:15:20Z

   On Thu, May 15, 2025, 07:27 PG Bug reporting form <noreply@postgresql.org>
   wrote:
   
   > The following bug has been logged on the website:
   >
   > Bug reference:      18930
   > 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 queries:
   > SELECT * FROM empty_table INTERSECT SELECT * FROM not_empty_table;
   > SELECT * FROM not_empty_table INTERSECT SELECT * FROM empty_table;
   > Obviously, the above queries always returns an empty set. I think it should
   > return an empty set quickly.
   >
   
   Also not a bug.
   
   David J.