cockroachdb / cockroachdb/cockroach

opt: constrain lookup joins with variable upper and lower bound

Open
#153,429 0 comments 0 reactions 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

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

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.