cockroachdb / cockroachdb/cockroach

opt: explore lookup join with `IS NOT DISTINCT FROM` equality

Open
#100,855 1 comment 1 reaction 0 assignees View on GitHub
C-enhancement T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

The optimizer does not currently explore lookup joins when the join condition contains an equality with an `IS NOT DISTINCT FROM` expression:

```
CREATE TABLE t (
a INT,
INDEX (a)
);

EXPLAIN (OPT, VERBOSE)
SELECT * FROM t t1 INNER LOOKUP JOIN t t2 ON t1.a IS NOT DISTINCT FROM t2.a;
info
-------------------------------------------------------------------
distribute
├── columns: a:1 a:5
├── stats: [rows=333333.3]
├── cost: 1e+100
├── distribution: us-east1
├── input distribution:
└── inner-join (cross)
├── columns: t1.a:1 t2.a:5
├── flags: force lookup join (into right side)
├── stats: [rows=333333.3]
├── cost: 1e+100
├── scan t [as=t1]
│ ├── columns: t1.a:1
│ ├── stats: [rows=1000]
│ ├── cost: 1084.62
│ ├── prune: (1)
│ └── unfiltered-cols: (1-4)
├── scan t [as=t2]
│ ├── columns: t2.a:5
│ ├── stats: [rows=1000]
│ ├── cost: 1084.62
│ ├── prune: (5)
│ └── unfiltered-cols: (5-8)
└── filters
└── t1.a:1 IS NOT DISTINCT FROM t2.a:5 [outer=(1,5)]
(25 rows)
```

I believe a lookup join is possible here, so we should explore one.

Jira issue: CRDB-26655

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.