cockroachdb / cockroachdb/cockroach
opt: propagate properties through some projects
Open
A-sql-optimizer
C-performance
T-sql-queries
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
```sql
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false;
CREATE TABLE kv (k INT PRIMARY KEY, v INT);
EXPLAIN SELECT * FROM kv AS t1, kv AS t2 WHERE t1.k = t2.k + 1;
```
I'm surprised that in the hash join only the left equality columns marked as a key - `t2.k` is a key, so simple expressions like `t2.k + 1` preserve that property (perhaps modulo overflow?). We should propagate it if possible.
The same thing for ordering:
```sql
EXPLAIN SELECT * FROM kv AS t1 INNER MERGE JOIN kv AS t2 ON t1.k = t2.k + 1;
```
we shouldn't need to sort the right input to the merge join.
Jira issue: CRDB-45619
Contributor guide
Assessment
This issue has not been assessed yet.