Thank you very much for your help. I didn’t know of a way to exclude OFFSET, but unfortunately it didn’t help me.
Excluded subjectId, id and selected category are unique for each user (users themselves determine which categories they want to see, and which documents should be excluded from the results). Performance is still significantly reduced even with a small increase in elements in the arrays. An array of categories can have no more than 20 values, but an array of excluded documents, in theory, can be any size the user wants.
I ran each query three times and gave the average execution time:
SELECT d.* , META(d).id
FROM MyData AS d
WHERE ANY cat IN category SATISFIES cat IN [1,18] END
and subjectId not in [1,2,3,4,5,6,7,8,9,10]
and id not in [100,200,300,400,500,600,700,800,900,1000]
AND someTs is not missing
AND META(d).id is not missing
ORDER BY someTs DESC, META(d).id DESC
LIMIT 20;
//511ms
//Add 2 elements to each array
SELECT d.* , META(d).id
FROM MyData AS d
WHERE ANY cat IN category SATISFIES cat IN [1,3,4,18] END
and subjectId not in [1,2,3,4,5,6,7,8,9,10,11,12]
and id not in [100,200,300,400,500,600,700,800,900,1000,1100,1200]
AND someTs is not missing
AND META(d).id is not missing
ORDER BY someTs DESC, META(d).id DESC
LIMIT 20 ;
//1.27s
//Add 4 more elements to each array
SELECT d.* , META(d).id
FROM MyData AS d
WHERE ANY cat IN category SATISFIES cat IN [1,3,4,5,6,7,8,18] END
and subjectId not in [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16]
and id not in [100,200,300,400,500,600,700,800,900,1000,1100,1200,1300,1400,1500,1600]
AND someTs is not missing
AND META(d).id is not missing
ORDER BY someTs DESC, META(d).id DESC
LIMIT 20 ;
//3.9s
New index ix1 contains 10340 items with 100% resident ratio, machine has 6 core/12 threads CPU with 15/32 GB used RAM with Couchbase 6.6.
When I ran the last query I noticed that the CPU load went from 6 to 22%, and the beggining of plan output as follow (64k lines long at all):
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"servTime": "975.6µs"
},
"privileges": {
"List": [
{
"Target": "default:MyData",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1
},
"~children": [
{
"#operator": "DistinctScan",
"#stats": {
"#itemsIn": 385,
"#itemsOut": 384,
"#phaseSwitches": 1543,
"kernTime": "3.7741828s"
},
"scan": {
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 385,
"#phaseSwitches": 1545,
"execTime": "1.9312ms",
"servTime": "3.7722516s"
},
"as": "d",
"index": "ix1",
...