Details
Major Description
This is based on a customer case.
Based on DDL+optimizer trace, I've created a syntethic testcase. The SQL script is attached to the linked issue.
Failure scenario is as follows:
set optimizer_switch='mrr=on';
|
Can also optionally have mrr_cost_based=on.
create table t1 ( |
col1,
|
col2,
|
INDEX idx1(col1), |
INDEX idx2(col1, col2) |
);
|
and a query
select * from t1 |
where |
col1=const and |
col2 IN (...) |
Then optimization runs like this
"range_scan_alternatives": [ |
{
|
"index": "idx2", |
"ranges": [ |
-- single-point range here
|
],
|
"rowid_ordered": false, |
"using_mrr": true, --- IMPORTANT |
"index_only": false, |
"rows": (good number) |
"cost": (also good) |
"chosen": true |
}
|
{
|
"index": "idx1", |
-- Doesn't matter
|
"rows": ..., -- Much worse than above |
"chosen": false, |
"cause": "cost" |
}
|
Then in best_access_path we will have this:
{
|
"access_type": "ref", |
"index": "idx1", |
"used_range_estimates": true, |
...
|
"rows": ... |
"cost": NEGATIVE_NUMBER |
"chosen": true |
},
|
Because the cost is negative, the optimizer will use ref(idx1) instead of range(idx2) which was much cheaper.