Details
Description
Taking an example from the array indexing docs, you can use array indexes to cover ANY ... IN ... SATISFIES queries.
For example:
CREATE INDEX idx_sched_covered_simple ON `travel-sample`
|
( DISTINCT ARRAY v.flight FOR v IN schedule END)
|
Completely covers:
SELECT meta().id FROM `travel-sample`
|
WHERE ANY v IN schedule SATISFIES v.flight LIKE 'UA%' END;
|
Excerpt of explain plan showing this:
"#operator": "IndexScan3",
|
"covers": [
|
"cover ((distinct (array (`v`.`flight`) for `v` in (`travel-sample`.`schedule`) end)))",
|
"cover ((meta(`travel-sample`).`id`))"
|
],
|
"filter_covers": {
|
"cover (any `v` in (`travel-sample`.`schedule`) satisfies ((\"UA\" <= (`v`.`flight`)) and ((`v`.`flight`) < \"UB\")) end)": true,
|
"cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) like \"UA%\") end)": true
|
},
|
"index": "idx_sched_covered_simple",
|
However this seems to break if your string contains certain characters, for example adding a . to the string:
SELECT meta().id FROM `travel-sample`
|
WHERE ANY v IN schedule SATISFIES v.flight LIKE 'U.A%' END;
|
Explain plan:
"#operator": "IndexScan3",
|
"index": "idx_sched_covered_simple",
|
"index_id": "cbb673e320dc5cde",
|
"index_projection": {
|
"primary_key": true
|
}
|
This causes vast overfetching to occur and hurts the query performance.
From brief testing this also happens for characters such as [, $, -, +, * but not other such as =, ~, <.
Attachments
Issue Links
- duplicates
-
MB-31575 Pushdown does not occur with LIKE predicate that contain "."
-
- Resolved
-