short-circuit in multiple tables cross join with empty build input

[jinhui-lai]

Describe the problem

Hi, CockroachDB developers,

1. two tables cross join

If CockroachDB has stats of empty_table before executing the following two tables cross join queries, CockroachDB will return an empty set immediately. I think it is reasonable.

SELECT * FROM empty_table CROSS JOIN not_empty_table;
SELECT * FROM not_empty_table CROSS JOIN empty_table;

2. multiple tables cross join

However, even though CockroachDB has stats of empty_table before executing the following multiple tables cross join queries, CockroachDB still wastes much time executing them.

SELECT * FROM empty_table CROSS JOIN not_empty_table1 CROSS JOIN not_empty_table2;
SELECT * FROM not_empty_table1 CROSS JOIN not_empty_table2 CROSS JOIN empty_table;

I think this is a common case in actual production scenarios. It's important to clarify that users might not intentionally perform CROSS JOIN 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 CockroachDB can address this performance bug, it would significantly improve query efficiency and save users' time in such cases.

Thank you for your time. Looking forward to your reply!

To Reproduce
You can reproduce it as follows:

1. Create tables and insert data

root@localhost:26257/defaultdb> CREATE TABLE t0(c0 INT8);                                                                                             
CREATE TABLE

Time: 23ms total (execution 18ms / network 5ms)

root@localhost:26257/defaultdb> CREATE TABLE t1(c1 INT8);                                                                                             
CREATE TABLE

Time: 20ms total (execution 15ms / network 5ms)

root@localhost:26257/defaultdb> CREATE TABLE t2(c2 INT8);                                                                                             
CREATE TABLE

Time: 22ms total (execution 17ms / network 5ms)

root@localhost:26257/defaultdb> INSERT INTO t1 SELECT i FROM generate_series(1, 10000000) AS i;                                                       
INSERT 0 10000000

Time: 60.606s total (execution 60.605s / network 0.001s)

root@localhost:26257/defaultdb> INSERT INTO t2 SELECT i FROM generate_series(1, 10000000) AS i;                                                       
INSERT 0 10000000

Time: 88.626s total (execution 88.621s / network 0.005s)

2. Check the query plan: CockroachDB has stats all tables

root@localhost:26257/defaultdb> explain SELECT * FROM t0 CROSS JOIN t1 CROSS JOIN t2;                                                                 
                                          info
----------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • cross join
  │ estimated row count: 1
  │
  ├── • cross join
  │   │ estimated row count: 1
  │   │
  │   ├── • scan
  │   │     estimated row count: 1 (100% of the table; stats collected 4 minutes ago)
  │   │     table: t0@t0_pkey
  │   │     spans: FULL SCAN
  │   │
  │   └── • scan
  │         estimated row count: 1 (100% of the table; stats collected 51 seconds ago)
  │         table: t1@t1_pkey
  │         spans: FULL SCAN
  │
  └── • scan
        estimated row count: 1 (100% of the table; stats collected 3 minutes ago)
        table: t2@t2_pkey
        spans: FULL SCAN
(23 rows)

Time: 3ms total (execution 2ms / network 0ms)

3. Execute the multiple tables cross join query: waste much time

root@localhost:26257/defaultdb> SELECT * FROM t0 CROSS JOIN t1 CROSS JOIN t2;                                                                         
  c0 | c1 | c2
-----+----+-----
(0 rows)

Time: 100.475s total (execution 100.475s / network 0.000s)

Expected behavior
The query should return an empty set quickly.

root@localhost:26257/defaultdb> SELECT * FROM t0 CROSS JOIN t1 CROSS JOIN t2;                                                                         
  c0 | c1 | c2
-----+----+-----
(0 rows)

Time: 0.0xxs total (execution 0.0xx s / network 0.008s)

Environment:
Server version: CockroachDB CCL v24.3.13 (x86_64-pc-linux-gnu, built 2025/05/13 17:14:36, go1.22.8X:nocoverageredesign) (same version as client)
Client app: cockroach sql

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-queries (found keywords: vectorized,plan,import)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[mw5h]

Hi @jinhui-lai, we agree that this is a potentially useful optimization and will look at making the necessary code changes when a resource becomes available.