dolthub / dolthub/dolt

Bad index plan for lookup on multiple columns.

Open
#8,884 0 comments 0 reactions 0 assignees View on GitHub
analyzer performance sql
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.