cockroachdb / cockroachdb/cockroach
opt: explore lookup join with `IS NOT DISTINCT FROM` equality
- 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
Assessment
This issue has not been assessed yet.