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 p.data.array[*].
value, this is what I’m using in my index also.
CREATE INDEX covering_array_idx ON default(ALL ARRAY d.data.array[*].`value` FOR d IN OBJECT_INNER_VALUES(data) WHEN ARRAY_LENGTH(d.data.array[*].`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.
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 - [220.127.116.11:8093][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.