[N1QL] query sometime uses intersect scan (mostly) sometimes union scan

Will attach a backup of the data, but basically the explain query sometimes returns that it is using an intersect scan and sometimes it returns that it is using a union scan.

`idxVM` ON `default`((distinct (array (`x`.`RAM`) for `x` in `VMs` end)))

`idxjoin_yr` ON `default`((distinct (array `v` for `v` in `join_yr` end)))

primary

1 bucket 10k docs on default 

Sample doc:

{
"tasks": [{
"Marketing": [{
"region2": "International",
"region1": "South"
}, {
"region2": "South"
}],
"Developer": ["IOS", "Indexing"]
}, "Sales", "QA"],
"name": [{
"FirstName": "employeefirstname-9"
}, {
"MiddleName": "employeemiddlename-9"
}, {
"LastName": "employeelastname-9"
}],
"address": [
[{
"city": "Delhi"
}, {
"street": "12th street"
}],
[{
"country": "EUROPE",
"apartment": 123
}]
],
"email": "9-mail@couchbase.com",
"mutated": 0,
"hobbies": {
"hobby": [{
"sports": ["Badminton", "Football", "Basketball"]
}, {
"dance": ["hip hop", "bollywood", "contemporary"]
}, "art"]
},
"department": "Support",
"join_yr": [2013, 2015, 2012],
"_id": "query-testemployee10153.1877827-0",
"VMs": [{
"RAM": 10,
"os": "ubuntu",
"name": "vm_10",
"memory": 10
}, {
"RAM": 10,
"os": "windows",
"name": "vm_11",
"memory": 10
}, {
"RAM": 10,
"os": "centos",
"name": "vm_12",
"memory": 10
}, {
"RAM": 10,
"os": "macos",
"name": "vm_13",
"memory": 10
}]
}

Query:

EXPLAIN select name from default where any v in default.join_yr satisfies v = 2016 END AND (ANY x IN default.VMs SATISFIES x.RAM between 1 and 5 END) AND  NOT (department = 'Manager') ORDER BY name limit 10

{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "UnionScan",
"scans": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"index": "idxjoin_yr",
"index_id": "ffc13501427524fd",
"index_projection": {
"primary_key": true
},
"keyspace": "default",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "2016",
"inclusion": 3,
"low": "2016"
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"index": "idxVM",
"index_id": "bbb9acd8f7c78b33",
"index_projection": {
"primary_key": true
},
"keyspace": "default",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "5",
"inclusion": 3,
"low": "1"
}
]
}
],
"using": "gsi"
}
}
]
},
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"index": "idxVM",
"index_id": "bbb9acd8f7c78b33",
"index_projection": {
"primary_key": true
},
"keyspace": "default",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "5",
"inclusion": 3,
"low": "1"
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"index": "idxjoin_yr",
"index_id": "ffc13501427524fd",
"index_projection": {
"primary_key": true
},
"keyspace": "default",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "2016",
"inclusion": 3,
"low": "2016"
}
]
}
],
"using": "gsi"
}
}
]
}
]
},
{
"#operator": "Fetch",
"keyspace": "default",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((any `v` in (`default`.`join_yr`) satisfies (`v` = 2016) end and any `x` in (`default`.`VMs`) satisfies ((`x`.`RAM`) between 1 and 5) end) and (not ((`default`.`department`) = \"Manager\")))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`default`.`name`)"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"limit": "10",
"sort_terms": [
{
"expr": "(`default`.`name`)"
}
]
},
{
"#operator": "Limit",
"expr": "10"
},
{
"#operator": "FinalProject"
}
]
},
"text": "select name from default where any v in default.join_yr satisfies v = 2016 END AND (ANY x IN default.VMs SATISFIES x.RAM between 1 and 5 END) AND NOT (department = 'Manager') ORDER BY name limit 10"
}
]

But also sometimes it returns:

[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"index": "idxVM",
"index_id": "bbb9acd8f7c78b33",
"index_projection": {
"primary_key": true
},
"keyspace": "default",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "5",
"inclusion": 3,
"low": "1"
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"index": "idxjoin_yr",
"index_id": "ffc13501427524fd",
"index_projection": {
"primary_key": true
},
"keyspace": "default",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "2016",
"inclusion": 3,
"low": "2016"
}
]
}
],
"using": "gsi"
}
}
]
},
{
"#operator": "Fetch",
"keyspace": "default",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((any `v` in (`default`.`join_yr`) satisfies (`v` = 2016) end and any `x` in (`default`.`VMs`) satisfies ((`x`.`RAM`) between 1 and 5) end) and (not ((`default`.`department`) = \"Manager\")))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`default`.`name`)"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"limit": "10",
"sort_terms": [
{
"expr": "(`default`.`name`)"
}
]
},
{
"#operator": "Limit",
"expr": "10"
},
{
"#operator": "FinalProject"
}
]
},
"text": "select name from default where any v in default.join_yr satisfies v = 2016 END AND (ANY x IN default.VMs SATISFIES x.RAM between 1 and 5 END) AND NOT (department = 'Manager') ORDER BY name limit 10"
}
]