Description
Indexes created:
CREATE INDEX `idxjoin_yr` ON `default`(array `v` for `v` in `join_yr` end) WITH(
)
and
CREATE INDEX `idxjoin_yr2` ON `default`((distinct (array `v` for `v` in `join_yr` end))) WITH(
{"index_type" : "forestdb"})
Query issued:
EXPLAIN select name from default use index (`idxjoin_yr2`) where any v in default.join_yr satisfies v = 2016 END;
uses the correct index
while same query with idxjoin_yr:
EXPLAIN select name from default use index (`idxjoin_yr`) where any v in default.join_yr satisfies v = 2016 END;
uses primary scan instead of index scan.
Dataset: Bucket default with documents like:
{
"tasks": [
{
"Marketing": [
,
{ "region2": "South" } ],
"Developer": [
"IOS",
"Indexing"
]
},
"Sales",
"QA"
],
"name": [
,
,
{ "LastName": "employeelastname-9" } ],
"address": [
[
,
{ "street": "12th street" } ],
[
]
],
"email": "9-mail@couchbase.com",
"mutated": 0,
"hobbies": {
"hobby": [
,
,
"art"
]
},
"department": "Support",
"join_yr": [
2013,
2015,
2012
],
"_id": "query-testemployee10153.1877827-0",
"VMs": [
,
,
,
{ "RAM": 10, "os": "macos", "name": "vm_13", "memory": 10 } ]
}