cockroachdb / cockroachdb/cockroach

opt: transform JOIN with equality on VALUES column to IN

Open
#149,272 0 comments 0 reactions 0 assignees View on GitHub
C-enhancement O-support P-3 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

We can transform equality joins where one side of the join is a Values expression into a Select expression. For example, this query:

```sql
SELECT * FROM t
JOIN (
SELECT i FROM (
VALUES (0), (10), (20), (30), (40), (50)
) AS v(i)
) AS v(i) ON t.i = v.i
```

Can be rewritten as:

```sql
SELECT * FROM t
WHERE i IN (
SELECT i FROM (
VALUES (0), (10), (20), (30), (40), (50)
) AS v(i)
)
```

The benefits of this rewrite include:

1. Simplifies the query plan.
2. May yield better row count estimates.
3. Avoids join reordering.

However, a hash-join may be more efficient than the binary search of `IN` evaluation as the number of items in the values expression increases. Therefore this transformation may not be an improvement in all cases.

Jira issue: CRDB-52078

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.