Array Covering index with UNNEST and condition

In CB 5.0.1-5003 also it uses covering index

Finally it’s version 5.0.0 build 3519 that I’m using

In CB 5.0.0-3519 also it uses covering index

I’ve tried several times, it doesn’t work! The requests are unusable since it’s do a continuous FS!

Maybe the problem is that I over simplified the model here. Actually, to get the array value, I need to do[1][*].value, this is what I’m using in my index also.

CREATE INDEX covering_array_idx ON default(ALL ARRAY[1][*].`value` FOR d IN OBJECT_INNER_VALUES(data) WHEN ARRAY_LENGTH([1][*].`value`) = 1 END, meta().cas, `key`, OBJECT_INNER_VALUES(data))
    WHERE type = 'product'

I have to do that as the model is a legacy one, and I can’t change it for now.

Did your previous query/index worked. Your latest post talking about different index.
When Index/query combinations are complex the non-covering might be right option to give right results.

Now I have decent perfs (~5000 results/s) with the following indexes :

CREATE INDEX array_idx ON default(ALL ARRAY d.`array` FOR d IN OBJECT_INNER_VALUES(data) WHEN ARRAY_LENGTH(d.`array`) = 1 END,
meta().cas, `key`, OBJECT_INNER_VALUES(data))
    WHERE type = 'product';

CREATE INDEX cas ON default(meta().cas)
    WHERE type = 'product';

It does an Intersect Scan between the two indexes. And now if I have no updates no full scan is performed, and the response is very quick. It still non covering query.

PS : I tested a lot of combination, and for reasons I don’t understand, key should absolutely be placed after meta().cas and OBJECT_INNER_VALUES(data) in last. And even if it does an intersect scan, I have completely different performances if I don’t add meta().cas to the first index.

Index follows b-tree. Query predicates are leading index key the value passed to indexer eliminated early.
non leading index key in predicate needs to applied by query engine.

Check this out

I’ve claimed victory too early !

I have really weird behavior. Sometimes my request are really quick, with even 7k results/s, and sometimes, it is very poor (500 results/s), even if there is absolutely no activity in Couchbase! Do you have any idea why? and how can I fix it?

Worst, sometimes, when I have very poor perfs, after a while, I’m getting disconnected from CB, and the request hangs.

2018-04-10 17:55:42,417 | INFO  | cb-io-1-5 | Endpoint - [][QueryEndpoint]: Got notified from Channel as inactive, attempting reconnect.

Avoid IntesectScan try to use USE INDEX. If you need help provide query/index together in same post

The best index I could come up with is the one in the latest post.

If I use USE INDEX, the performances are ok without cas, but it does a full scan with it and it is not usable, I often have index scan timeout.

Finally I dropped N1QL and used Views instead. I have now a really much better performances (~15s to get everything).

I’m disappointed because I wanted to push for the use of N1QL in our project, but this is a failure…

Thank you for your help.