Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.6.0, 7.2.4, 7.1.6
-
Untriaged
-
0
-
Unknown
Description
The planner may not pick up a covering index if the index has complex WHERE clause.
Repro:
CREATE INDEX ishix3 ON default(c5, c6, c7, c8, c9);
|
CREATE INDEX ishix4 ON default(c5, c8, c10) WHERE c6 != 1 AND c7 NOT IN [1,2] AND c11 != 0;
|
|
|
explain SELECT 1 FROM default WHERE c5 = 10 AND c8 >= 11 AND c8 <= 20 AND c6 != 1 AND c7 NOT IN [1,2] AND c11 != 0;
|
The planner picks ishix3. If forced, it can use ishix4:
explain SELECT 1 FROM default USE INDEX (ishix4) WHERE c5 = 10 AND c8 >= 11 AND c8 <= 20 AND c6 != 1 AND c7 NOT IN [1,2] AND c11 != 0;
|