Array index never used even when forced

SQL++
,
7

Hi @atom_yang, the query might work and give results. Can you check if EXPLAIN of your query is using the index.

Hi @dagneytaggartsv,
try using following index and query:

CREATE INDEX iannot_so_exp1 ON default (DISTINCT ARRAY 
                          (DISTINCT ARRAY obj1 FOR obj1 IN obj.so END) 
                          FOR obj in annot.ct END,  annot.ct);

explain select obj.so from default v  
unnest v.annot.ct AS obj 
unnest obj.so as obj1 
where  obj1 = 123 ;

…
"#operator": “DistinctScan”,
“scan”: {
"#operator": “IndexScan”,
“covers”: [
“cover ((distinct (array (distinct (array obj1 for obj1 in (obj.so) end)) for obj in ((v.annot).ct) end)))”,
“cover (((v.annot).ct))”,
“cover ((v.annot))”,
“cover ((meta(v).id))”
],
“index”: “iannot_so_exp1”,
“index_id”: “27c322d9131d40ce”,
“keyspace”: “default”,

Basically, it fixes two issues in your queries:

  1. To qualify an index for a query, it is necessary for the query/SELECT to have a where clause predicates that include the various index-keys in the order they are defined in CREATE INDEX. This is a generic index keys prefix-matching requirement for any composite indexes that use b-tress (including all traditional databases). You will see more details in docs, but for now, you can refer to some detail I provided in Covering index range spans

In your query, the predicate does not use the index keys annot and annot.ct in where-clause predicates. In the above index def, I changed the order of index keys (note, I also remove ‘annot’ as that isn’t needed to cover this query). To use your index, the query should have additional predicates using ‘annot’ and ‘annot.ct’, something like:
'annot' IS NOT MISSING and 'annot.ct' IS NOT MISSING

  1. your SELECT query predicate should use nested UNNESTs or nested ANY constructs in the where clause to use the nested array index. AFAIK, you can’t use UNNEST in one level, and ANY in another level. For ex: following query with nested ANYs would also work fine:
    explain select meta(v).id from default v
    where ANY obj in v.annot.ct satisfies
    (ANY obj1 in obj.so satisfies obj1 = 123 END) END;
    …
    "#operator": “IndexScan”,
    “covers”: [
    “cover ((distinct (array (distinct (array obj1 for obj1 in (obj.so) end)) for obj in ((v.annot).ct) end)))”,
    “cover (((v.annot).ct))”,
    “cover ((v.annot))”,
    “cover ((meta(v).id))”
    ],
    “index”: “iannot_so_exp1”,
    “index_id”: “27c322d9131d40ce”,
    “keyspace”: “default”,
    hth,
    -Prasad
2 Likes