Array Covering index with UNNEST and condition

SQL++
,
21

In CB 5.0.1-5003 also it uses covering index

22

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

23

In CB 5.0.0-3519 also it uses covering index

24

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 p.data.array[1][*].value, this is what I’m using in my index also.

CREATE INDEX covering_array_idx ON default(ALL ARRAY d.data.array[1][*].`value` FOR d IN OBJECT_INNER_VALUES(data) WHEN ARRAY_LENGTH(d.data.array[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.

25

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.

26

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.

27

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 http://blog.couchbase.com/wp-content/uploads/2017/03/N1QL-A-Practical-Guide-v2.pdf

28

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 - [192.160.79.226:8093][QueryEndpoint]: Got notified from Channel as inactive, attempting reconnect.
29

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

30

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.

31

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.