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;
|
Attachments
For Gerrit Dashboard: MB-61014 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
206590,2 | MB-61014 Keep index with WHERE clause ... | trinity | query | Status: MERGED | +2 | +1 |
206594,2 | MB-61014 Keep index with WHERE clause ... | neo | query | Status: MERGED | +2 | +1 |
206663,3 | MB-61014 Limit UNION SCAN generation | trinity | query | Status: MERGED | +2 | +1 |
206667,2 | MB-61014 Limit UNION SCAN generation | neo | query | Status: MERGED | +2 | +1 |
207130,2 | MB-61014 Keep index with WHERE clause ... | 7.2.4 | query | Status: MERGED | +2 | +1 |