Details
-
Improvement
-
Resolution: Unresolved
-
Minor
-
4.6.2
-
None
Description
For simple use cases, ARRAY_CONTAINS is functionally equivalent to a more complex ANY x IN y SATISFIES z END statement. Specifically, if the SATISFIES predicate is simply testing to see if x = some value. In this case, the more complex ANY statement will make use of array indexes, but the simpler ARRAY_CONTAINS does not. It should be theoretically possible to recognize the equivalence of these clauses and match the index.
Example index:
CREATE INDEX test ON default (DISTINCT ARRAY x FOR x IN arrayProp END) WHERE type = 'test' |
Example ANY query (resulting plan attached):
SELECT * FROM default WHERE type = 'test' AND ANY x IN arrayProp SATISFIES x = 'testValue' END |
Example ARRAY_CONTAINS query (resulting plan attached):
SELECT * FROM default WHERE type = 'test' AND ARRAY_CONTAINS(arrayProp, 'testValue') |
Note: This could cause some issues with the current requirement that the variable name in the ARRAY clause of the index match the variable name of the ANY clause of the query. It might be necessary to require a specific variable name such as "x" in the index in order to match ARRAY_CONTAINS.