Details
-
Improvement
-
Resolution: Unresolved
-
Major
-
Morpheus
-
0
Description
Secondary indexes currently cannot be utilized beyond simple range and point lookups. There's a missed opportunity to exploit secondary indexes for arbitrary expressions.
Example:
SELECT e.name, e.salary
|
FROM Employee e
|
WHERE lowercase(e.name) LIKE "%smith" |
If we have an index on Employee.name (call it name_idx), it won't be utilized.
However, we can do a scan on the secondary index and apply the expression on secondary key(s):
... <The rest is similar to what's currently done for unnest-map> ... |
select like(lowercase($$name), "%smith") |
data-scan []<-[$$name, $$PK] <- Employee.name_idx
|
Attachments
Issue Links
- blocks
-
MB-57274 Analytics fails to use indexes when faced with LIKE predicates
- Open