cockroachdb / cockroachdb/cockroach
opt: constrain lookup joins with variable upper and lower bound
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Example showing that we can build lookup join constraint expressions with a variable lower bound and a constant upper bound, but we do not build a similar constraint expression if both bounds are variable:
```sql
CREATE TABLE ab (
a INT PRIMARY KEY,
b INT NOT NULL
);
CREATE TABLE cd (
c INT,
d INT,
INDEX (c, d)
);
INSERT INTO ab VALUES
(1, 10),
(2, 20);
INSERT INTO cd VALUES
(1, 0),
(1, 10),
(1, 20),
(1, 100),
(2, 20),
(2, 30),
(2, 200);
-- Constant upper bound.
EXPLAIN
SELECT * FROM ab
INNER LOOKUP JOIN cd ON c=a AND d>b AND d<100;
-- info
-- -----------------------------------------------------------
-- distribution: local
-- vectorized: true
--
-- • lookup join
-- │ table: cd@cd_c_d_idx
-- │ lookup condition: ((d > b) AND (d < 100)) AND (a = c)
-- │
-- └── • scan
-- missing stats
-- table: ab@ab_pkey
-- spans: FULL SCAN
-- (11 rows)
SELECT * FROM ab
INNER LOOKUP JOIN cd ON c=a AND d>b AND d<100;
-- a | b | c | d
------+----+---+-----
-- 1 | 10 | 1 | 20
-- 2 | 20 | 2 | 30
--(2 rows)
-- Variable upper bound.
EXPLAIN
SELECT * FROM ab
INNER LOOKUP JOIN cd ON c=a AND d>b AND d b) AND (a = c)
-- │ pred: d < (c * 100)
-- │
-- └── • scan
-- missing stats
-- table: ab@ab_pkey
-- spans: FULL SCAN
-- (12 rows)
```
Jira issue: CRDB-54416
Contributor guide
Assessment
This issue has not been assessed yet.