Bad index plan for lookup on multiple columns.
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
Description
Repro steps:
```
create table test(pk int primary key, c0 int, key idx1(c0, pk));
insert into test values (1, 0), (2, 1), (3, 2), (4, 1), (5, 2), (6, 0), (7, 2), (8, 0), (9, 1);
describe plan select * from test where c0 = 1 and pk > 1;
alter table test add key idx2(c0);
describe plan select * from test where c0 = 1 and pk > 1;
```
The first describe shows the correct, optimal plan: an IndexedTableAccess doing a lookup on both columns.
```
+---------------------------------------+
| plan |
+---------------------------------------+
| Filter |
| ├─ ((test.c0 = 1) AND (test.pk > 1)) |
| └─ IndexedTableAccess(test) |
| ├─ index: [test.c0,test.pk] |
| ├─ filters: [{[1, 1], (1, ∞)}] |
| └─ columns: [pk c0] |
+---------------------------------------+
```
However, the second describe produces a suboptimal plan:
+---------------------------------------+
| plan |
+---------------------------------------+
| Filter |
| ├─ ((test.c0 = 1) AND (test.pk > 1)) |
| └─ IndexedTableAccess(test) |
| ├─ index: [test.c0] |
| ├─ filters: [{[1, 1]}] |
| └─ columns: [pk c0] |
+---------------------------------------+
There are two things wrong here:
1. The two indexes are identical. Literally. They have the exact same tree hash. This is because non-unique index keys append the primary key to the end in order to make them unique in the underlying map. Thus, although one index is declared as `(c0)` and the other is declared as `(c0, pk)`, they contain the exact same data and can optimize the exact same queries. Choosing `idx2` should not result in a worse plan.
2. Assuming that the engine doesn't know that the indexes are identical (and doesn't realize that `idx2` can be used to lookup both columns), it still shouldn't be choosing `idx2` for this query. It should deduce that `idx1` produces a better plan and choose that.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.