Details
Description
Playing with CBO on beer-sample, after doing:
ANALYZE ANALYTICS COLLECTION _default;
I noticed that:
SELECT * FROM _default WHERE city = 'San Diego';
uses a city index if you create one:
CREATE INDEX city_index ON _default(city: string);
However, the following seemingly equivalent query doesn't:
SELECT * FROM _default WHERE city LIKE 'San Diego';
Interestingly, the selectivity estimate for both queries is identical but the cost estimates and decision are not. For the equality case, the top of the plan says:
"optimizer-estimates":
{ "cardinality": 0.73, "op-cost": 0, "total-cost": 2.59 }vs. what it says for the LIKE case:
"optimizer-estimates":
{ "cardinality": 0.73, "op-cost": 0, "total-cost": 7336.16 }The index should be picked, one would think?
This is not CBO's fault, it seems, as if you disable CBO the behavior is the same. I confirmed that the candidate AsterixDB release shares this issue.
We should use the index in this case! (Both in RBO and in CBO.)