Long n1ql query time

SQL++
1

Couchbase 4.5.1-2844-1 CE. I have a cluster of 3 nodes 4-core CPU and 8Gb RAM.
Query execution takes from 10ms to several secs on covering index, no matter from workbech or from PHP SDK. This happens when cpu usage on node higher than ~70%. Most of CPU used by cbq-engine and memcached processes (although we do not use memcached buckets) and cluster has 0 queries/sec.
“Service ‘query’ exited with status 1” - the only error i found in logs.
Query example:
SELECT MAX(id) AS lastMessageId, COUNT(id) AS cnt, ARRAY_SORT([from, to]) AS withUser
FROM default
USE INDEX (idx_example)
WHERE (type = “message”) AND (from = 1)
GROUP BY ARRAY_SORT([from, to])

Index def:
CREATE INDEX idx_example ON default(type,from,to,id) WHERE (type = “message”)

2

I think i solve the problem.
Another query without covering index generated 10000 - 15000 ops/sec and high cpu usage. Now i need to rewrite my queries to use only covering index.

3

You can also use the following index.

CREATE INDEX idx_example ON default(from,to,id) WHERE (type = “message”)