Query with index performs unnecessary fetch

SQL++
,
1

Hi,
I have a following data in the bucket:
{“n”:“abc def”,“d”:1551918525,“t”:[“abc”, “def”],“l”:“pl”,“c”:2094,“y”:2019,“m”:3}

And the following index
CREATE INDEX tagindexwithid ON itemdata(d,(distinct (array k for k in t end)),(meta().id)) PARTITION BY hash(d)

I would expect that folliowing query :

select meta().id, d from itemdata m where d >= 1551916800 and d < 1557705600 AND ((((ANY t IN  t SATISFIES t  like "abc%" END )) AND NOT ((ANY t IN  t SATISFIES t  like "xxx%" END ))))

would not need to perform fetch, as all data is in index, however the query plan states, that the fetch is performed.
How to modify the index to make fetch unnecessary and for best performance of this query ?

Thank you.
Piotr


current query plan:

{
    "#operator": "Sequence",
    "~children": [
        {
            "#operator": "DistinctScan",
            "scan": {
                "#operator": "IndexScan3",
                "as": "m",
                "index": "tagindexwithid",
                "index_id": "b55e0ee9e13f6264",
                "index_projection": {
                    "primary_key": true
                },
                "keyspace": "itemdata",
                "namespace": "default",
                "spans": [
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "1557705600",
                                "inclusion": 1,
                                "low": "1551916800"
                            },
                            {
                                "high": "\"abd\"",
                                "inclusion": 1,
                                "low": "\"abc\""
                            }
                        ]
                    }
                ],
                "using": "gsi"
            }
        },
        {
            "#operator": "Fetch",
            "as": "m",
            "keyspace": "itemdata",
            "namespace": "default"
        },
        {
            "#operator": "Parallel",
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Filter",
                        "condition": "(((1551916800 <= (`m`.`d`)) and ((`m`.`d`) < 1557705600)) and (any `t` in (`m`.`t`) satisfies (`t` like \"abc%\") end and (not any `t` in (`m`.`t`) satisfies (`t` like \"xxx%\") end)))"
                    },
                    {
                        "#operator": "InitialProject",
                        "result_terms": [
                            {
                                "expr": "(meta(`m`).`id`)"
                            },
                            {
                                "expr": "(`m`.`d`)"
                            }
                        ]
                    },
                    {
                        "#operator": "FinalProject"
                    }
                ]
            }
        }
    ]
}
2

This particular query must do fetch and can’t use the index data and return results (covering).

The query uses Array Index. Implicit Array index key can be used for covering only when all predicates are pushed to indexscan and no false positives are possible. In this case only like “abc%” only passed to indexScan and NOT LIKE “xxx%” is not passed to the indexscan. So it must fetch the document.

Query has two ANY clauses on same array. i.e. It is looking ARRAY has “abc%” in any position AT same time it must also don’t have “xxxx%” in any position. implicit Array index will have only one value of array at any given time.

Covering purpose include array explicitly.

CREATE INDEX tagindexwithid ON itemdata ( d ,(distinct (array k for k in t end)), t) PARTITION BY hash( d )