Bad index plan for lookup on multiple columns.

[nicktobey]

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.