I have a Couchbase(4.5) bucket my-data
Document structure
{
_class: “com.dom.Activity”,
depId: 984,
dayIds: [17896, 17897, 17898],
startTime: 10,
endTime: 20
}
Index
CREATE INDEX idx_dep_day ON my-data(depId, distinct array i for i in dayIds end, , meta().id) WHERE _class = “com.dom.Activity” and startTime is not null and endTime is not null;
Query-1
select meta(b).id from
my-data b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984
and any i in dayIds satisfies i = 17896 end and startTime is not null and
meta().id > ‘Activity-a65e7e616f21e4c6d7b7bccbfd154da1’ and endTime is not null limit 80000
Explain-1
[
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “DistinctScan”,
“scan”: {
“#operator”: “IndexScan”,
“index”: “idx_dep_day”,
“index_id”: “53398c61c49ae09a”,
“keyspace”: “my-data”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“984”,
“17896”
],
“Inclusion”: 2,
“Low”: [
“984”,
“17896”,
"“Activity-a65e7e616f21e4c6d7b7bccbfd154da1"”
]
}
}
],
“using”: “gsi”
}
},
{
“#operator”: “Fetch”,
“as”: “b”,
“keyspace”: “my-data”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “(((((((b._class) = “com.dom.Activity”) and ((b.depId) = 984)) and any i in (b.dayIds) satisfies (i = 17896) end) and ((b.startTime) is not null)) and (“Activity-a65e7e616f21e4c6d7b7bccbfd154da1” < (meta(b).id))) and ((b.endTime) is not null))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(meta(b).id)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
{
“#operator”: “Limit”,
“expr”: “80000”
}
]
},
“text”: “select meta(b).id from
my-data b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984
and any i in dayIds satisfies i = 17896 end and startTime is not null and
meta().id > ‘Activity-a65e7e616f21e4c6d7b7bccbfd154da1’ and endTime is not null limit 80000”
}
]
But when I remove the array from the where clause
Query -2
select meta(b).id from
my-data b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984
and startTime is not null and
meta().id > ‘Activity-a65e7e616f21e4c6d7b7bccbfd154da1’ and endTime is not null limit 80000
Explain-2
[
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “DistinctScan”,
“scan”: {
“#operator”: “IndexScan”,
“covers”: [
“cover ((b.depId))”,
“cover ((distinct (array i for i in (b.dayIds) end)))”,
“cover ((meta(b).id))”,
“cover ((meta(b).id))”
],
“filter_covers”: {
“cover (((b.endTime) is not null))”: true,
“cover (((b.startTime) is not null))”: true,
“cover ((b._class))”: “com.dom.Activity”
},
“index”: “idx_dep_day”,
“index_id”: “53398c61c49ae09a”,
“keyspace”: “my-data”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“successor(984)”
],
“Inclusion”: 1,
“Low”: [
“984”
]
}
}
],
“using”: “gsi”
}
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “(((((cover ((b._class)) = “com.dom.ActivityImpl”) and (cover ((b.depId)) = 984)) and cover (((b.startTime) is not null))) and (“Activity-a65e7e616f21e4c6d7b7bccbfd154da1” < cover ((meta(b).id)))) and cover (((b.endTime) is not null)))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “cover (meta(b).id)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
{
“#operator”: “Limit”,
“expr”: “80000”
}
]
},
“text”: “select meta(b).id from
my-data b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984
and startTime is not null and
meta().id > ‘Activity-a65e7e616f21e4c6d7b7bccbfd154da1’ and endTime is not null limit 80000”
}
]
Why can’t I get the index covering when I use the dayIds array in the where clause?