Array index not being used by query

SQL++
, ,
7

Any Index to use query required predicate.

For UNNEST use the index
         * Array Index key needs to be leading Index key
         * Array Index key requires ALL keyword NO DISTINCT keyword (Unnest semantics does not eliminate duplicates)
         * Unnest Alias needs to be matched with Array Index variable
         * Predicate must contain  ARAAY variable and WHEN clause.

Example:

CREATE INDEX IX_visits_test
ON test(ALL ARRAY v.departmentCode FOR v IN visits END, visits) WHERE (type = "Visitor");

SELECT v FROM IX_visits_test AS d UNNEST d.visits AS v WHERE d.type = "Visitor" AND v.departmentCode "HS";

If those doesn’t match you can use Array IndexScan by providing ANY predicate and apply Unnest post scan

Example:

 CREATE INDEX IX_visits_test
    ON test(DISTINCT ARRAY v.departmentCode FOR v IN visits END, visits) WHERE (type = "Visitor");

 SELECT v FROM IX_visits_test AS d UNNEST d.visits AS v WHERE d.type = "Visitor" AND ANY v IN d.visits SATISFIES v.departmentCode  = "HS" END;

The use index predicate must contain leading index key. The reason is Indexer does not maintain data if the leading index key value evaluates to MISSING. Without predicate on leading key query wants to results of MISSING on that field, so index will not be chosen. You can provide explicit predicate WHERE <leading key> IS NOT MISSING