Thread

1. BUG #18933: PostgreSQL fails to handle JOIN operation involving Empty Table

   PG Bug reporting form <noreply@postgresql.org> — 2025-05-15T13:42:30Z

   The following bug has been logged on the website:
   
   Bug reference:      18933
   Logged by:          jinhui lai
   Email address:      jinhuilai@email.ncu.edu.cn
   PostgreSQL version: 17.5
   Operating system:   ubuntu 22.04
   Description:        
   
   Hi, PostgreSQL Developers,
   Hi, PostgreSQL Developers,
   Please consider such queries:
   SELECT * FROM empty_table CROSS  JOIN not_empty_table;
   SELECT * FROM not_empty_table CROSS  JOIN empty_table;
   SELECT * FROM empty_table, not_empty_table;
   SELECT * FROM not_empty_table, 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 JOIN operations when involving 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 performJOIN 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=# SELECT * FROM  pgbench_accounts AS a1 CROSS JOIN pgbench_accounts
   AS a2  CROSS JOIN empty_table;
   ^CCancel request sent
   ERROR:  canceling statement due to user request
   Time: 13009160.244 ms (03:36:49.160)
   testdb=# SELECT * FROM pgbench_accounts CROSS JOIN empty_table;
    aid | bid | abalance | filler | aid | bid | abalance | filler
   -----+-----+----------+--------+-----+-----+----------+--------
   (0 rows)
   Time: 9341.104 ms (00:09.341)
   testdb=# explain SELECT * FROM pgbench_accounts CROSS JOIN empty_table;
                                        QUERY PLAN
   -------------------------------------------------------------------------------------
    Nested Loop  (cost=0.00..265139357.62 rows=21000000000 width=449)
      ->  Seq Scan on pgbench_accounts  (cost=0.00..2639345.00 rows=100000000
   width=97)
      ->  Materialize  (cost=0.00..13.15 rows=210 width=352)
            ->  Seq Scan on empty_table  (cost=0.00..12.10 rows=210 width=352)
    JIT:
      Functions: 3
      Options: Inlining true, Optimization true, Expressions true, Deforming
   true
   (7 rows)
   Time: 1.241 ms
   testdb=# SELECT * FROM pgbench_accounts, empty_table;
    aid | bid | abalance | filler | aid | bid | abalance | filler
   -----+-----+----------+--------+-----+-----+----------+--------
   (0 rows)
   Time: 9499.105 ms (00:09.499)
   testdb=# explain SELECT * FROM pgbench_accounts, empty_table;
                                        QUERY PLAN
   -------------------------------------------------------------------------------------
    Nested Loop  (cost=0.00..265139357.62 rows=21000000000 width=449)
      ->  Seq Scan on pgbench_accounts  (cost=0.00..2639345.00 rows=100000000
   width=97)
      ->  Materialize  (cost=0.00..13.15 rows=210 width=352)
            ->  Seq Scan on empty_table  (cost=0.00..12.10 rows=210 width=352)
    JIT:
      Functions: 3
      Options: Inlining true, Optimization true, Expressions true, Deforming
   true
   (7 rows)
   Time: 1.147 ms
   testdb=# SELECT * FROM empty_table CROSS JOIN pgbench_accounts;
    aid | bid | abalance | filler | aid | bid | abalance | filler
   -----+-----+----------+--------+-----+-----+----------+--------
   (0 rows)
   Time: 9182.863 ms (00:09.183)
   testdb=# explain SELECT * FROM empty_table CROSS JOIN pgbench_accounts;
                                        QUERY PLAN
   -------------------------------------------------------------------------------------
    Nested Loop  (cost=0.00..265139357.62 rows=21000000000 width=449)
      ->  Seq Scan on pgbench_accounts  (cost=0.00..2639345.00 rows=100000000
   width=97)
      ->  Materialize  (cost=0.00..13.15 rows=210 width=352)
            ->  Seq Scan on empty_table  (cost=0.00..12.10 rows=210 width=352)
    JIT:
      Functions: 3
      Options: Inlining true, Optimization true, Expressions true, Deforming
   true
   (7 rows)
   Time: 1.118 ms
   testdb=# SELECT * FROM  pgbench_accounts AS a1 INNER JOIN pgbench_accounts
   AS a2 ON a1.aid =a2.aid CROSS JOIN empty_table;
    aid | bid | abalance | filler | aid | bid | abalance | filler | aid | bid |
   abalance | filler
   -----+-----+----------+--------+-----+-----+----------+--------+-----+-----+----------+--------
   (0 rows)
   Time: 34645.057 ms (00:34.645)
   
   

2. Re: BUG #18933: PostgreSQL fails to handle JOIN operation involving Empty Table

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-15T15:28:44Z

   PG Bug reporting form <noreply@postgresql.org> writes:
   > Obviously, the above queries always returns an empty set. I think it should
   > return an empty set quickly.
   
   None of these things are bugs.  You are accomplishing little except
   to annoy people, especially when the more appropriate way to proceed
   was already explained to you a few weeks ago.
   
   			regards, tom lane