I created the following index:
CREATE INDEX `idx_post_tags_score_desc` ON `bucket`((-`score`),`creationTime`,
`upvotes`,(`creator`.`followerCount`),`countComments`,
(distinct (array `t` for `t` in `tags` end)))
WHERE (`_class` = "some.package.Class") WITH { "num_replica":2 }
This is the query I want to execute:
SELECT * FROM `bucket`
WHERE `_class` = "some.package.Class"
AND -(-score) IS NOT NULL
AND creationTime >= 1499140972 AND creationTime <= 1530676972
AND upvotes >= 0 AND upvotes <= 182021
AND creator.followerCount >= 1000 AND creator.followerCount <= 8118747
AND countComments >= -1 AND countComments <= 10000000
AND id NOT IN ["06727cbe-485a-4ef4-b820-b373231ec336"]
AND (ANY AND EVERY t IN ["tag1", "tag2"] SATISFIES t IN tags END)
ORDER BY -score LIMIT 16 OFFSET 0
This is the explain of the query:
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan2",
"index": "idx_post_tags_score_desc",
"index_id": "3d18f2353d75c2cc",
"index_projection": {
"primary_key": true
},
"keyspace": "bucket",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"inclusion": 0,
"low": "null"
},
{
"high": "1530676972",
"inclusion": 3,
"low": "1499140972"
},
{
"high": "182021",
"inclusion": 3,
"low": "0"
},
{
"high": "8118747",
"inclusion": 3,
"low": "1000"
},
{
"high": "10000000",
"inclusion": 3,
"low": "(-1)"
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "Fetch",
"keyspace": "bucket",
"namespace": "default"
},
{
"#operator": "Parallel",
"maxParallelism": 1,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((((((((((((`bucket`.`_class`) = \"some.package.Class\") and ((-(`bucket`.`score`)) is not null)) and (1499140972 <= (`bucket`.`creationTime`))) and ((`bucket`.`creationTime`) <= 1530676972)) and (0 <= (`bucket`.`upvotes`))) and ((`bucket`.`upvotes`) <= 182021)) and (1000 <= ((`bucket`.`creator`).`followerCount`))) and (((`bucket`.`creator`).`followerCount`) <= 8118747)) and ((-1) <= (`bucket`.`countComments`))) and ((`bucket`.`countComments`) <= 10000000)) and (not ((`bucket`.`id`) in [\"06727cbe-485a-4ef4-b820-b373231ec336\"]))) and any and every `t` in [\"tag1\", \"tag2\"] satisfies (`t` in (`bucket`.`tags`)) end)"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"as": "_ID",
"expr": "(meta(`bucket`).`id`)"
},
{
"as": "_CAS",
"expr": "(meta(`bucket`).`cas`)"
},
{
"expr": "`bucket`",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "16"
}
]
},
"text": "SELECT META(`bucket`).id AS _ID, META(`bucket`).cas AS _CAS, `bucket`.* FROM `bucket` \nWHERE `_class` = \"some.package.Class\" \nAND -score IS NOT NULL \nAND creationTime >= 1499140972 AND creationTime <= 1530676972 \nAND upvotes >= 0 AND upvotes <= 182021\nAND creator.followerCount >= 1000 AND creator.followerCount <= 8118747 \nAND countComments >= -1 AND countComments <= 10000000 \nAND id NOT IN [\"06727cbe-485a-4ef4-b820-b373231ec336\"] \nAND (ANY AND EVERY t IN [\"tag1\", \"tag2\"] SATISFIES t IN tags END) ORDER BY -score LIMIT 16 OFFSET 0"
}
In the query web console the execution takes 1.00m. The same query in my Spring backend seems to execute faster (about 2000 ms) (don’t know why?). But even 2s is quite slow. Is the index or query correctly implemented? Is there any way to improve execution time? There are 264,385 documents of type Class in the database, 3,783,270 documents total. The couchbase cluster contains 3 nodes (4 cores, 32gb ram each).