cockroachdb / cockroachdb/cockroach
opt: eliminate unnecessary index-joins on top of indexes with virtual computed columns
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Consider the table and query:
```sql
CREATE TABLE t (
a INT PRIMARY KEY,
b JSONB,
c STRING AS (b->>'c') VIRTUAL,
INDEX (c)
);
SELECT a, c FROM t WHERE c = 'foo';
```
The query plan includes an unnecessary index-join:
```
project
├── columns: a:1!null c:3
├── immutable
├── key: (1)
├── fd: (1)-->(3)
├── index-join t
│ ├── columns: a:1!null b:2
│ ├── immutable
│ ├── key: (1)
│ ├── fd: (1)-->(2)
│ └── scan t@t_c_idx
│ ├── columns: a:1!null
│ ├── constraint: /3/1: [/'foo' - /'foo']
│ └── key: (1)
└── projections
└── b:2->>'c' [as=c:3, outer=(2), immutable]
```
Notice that the index can produce column `c`, but instead we perform an index-join to retrieve `b` and compute `c` from it.
The index-join is created during the `GenerateConstrainedScans` exploration rule. This rule matches a `(Select (Scan))` expression which does not produce column `c:3`. `c:3` is produced in a different memo-group. The new expression generated by `GenerateConstrainedScans` must produce the same columns as the `Select` expression it transforms. Furthermore, the because the rule does not match on a Project, it is unaware that column `c` needs to be produced at all—it's operating on a sub-tree where `c` is not an output column. Here's a look at the diff between the matched and new expression:
The primary difficulty in addressing this is that the `Project` is not guaranteed to be directly above the index-join. Therefore, we can't cover all cases by matching on the `(Project (IndexJoin (Scan)))` pattern and generating an alternative expression. But that would work for some cases.
Note: This is somewhat similar to #54588 and #53586.
Jira issue: CRDB-38285
Contributor guide
Assessment
This issue has not been assessed yet.