Description
First off a build this query had the correct plan in was 7.0.0-1325
1 bucket, 1 node kv index n1ql
1 secondary index 1 primary index
CREATE INDEX `idxVM2` ON `default`((all (array (`x`.`RAM`) for `x` within `VMs` end)),`VMs`)
SELECT x
FROM default emp1 USE INDEX(idxVM2)
UNNEST emp1.VMs AS x
JOIN default task ON KEYS META(`emp1`).id
WHERE x.RAM > 1
AND x.RAM < 5
This query in 1350 returns this plan
{'#operator': 'Sequence', '~children': [{'#operator': 'PrimaryScan3', 'as': 'emp1', 'index': '#primary', 'index_projection':
, 'keyspace': 'default', 'namespace': 'default', 'using': 'gsi'},
{'#operator': 'Fetch', 'as': 'emp1', 'keyspace': 'default', 'namespace': 'default'}, {'#operator': 'Parallel', '~child': {'#operator': 'Sequence', '~children': [
{'#operator': 'Filter', 'condition': 'is_array((`emp1`.`VMs`))'},
{'#operator': 'Unnest', 'as': 'x', 'expr': '(`emp1`.`VMs`)', 'filter': '((1 < (`x`.`RAM`)) and ((`x`.`RAM`) < 5) and (`x` is not missing))'}]}},
{'#operator': 'Join', 'as': 'task', 'keyspace': 'default', 'namespace': 'default', 'on_keys': '(meta(`emp1`).`id`)'}, {'#operator': 'Parallel', '~child': {'#operator': 'Sequence', '~children': [{'#operator': 'InitialProject', 'result_terms': [
{'expr': '`x`'}]}]}}]}
in 1325 however it was using the correct index and was covered properly
{'#operator': 'Sequence', '~children': [{'#operator': 'DistinctScan', 'scan': {'#operator': 'IndexScan3', 'as': 'emp1', 'covers': ['cover ((all (array (`x`.`RAM`) for `x` within (`emp1`.`VMs`) end)))', 'cover ((`emp1`.`VMs`))', 'cover ((meta(`emp1`).`id`))'], 'filter': 'is_array(cover ((`emp1`.`VMs`)))', 'index': 'idxVM2', 'index_id': '74018268a4c7493b', 'index_projection':
, 'keyspace': 'default', 'namespace': 'default', 'spans': [{'exact': True, 'range': [
{'high': '5', 'inclusion': 0, 'index_key': '(all (array (`x`.`RAM`) for `x` within `VMs` end))', 'low': '1'}, {'high': '{}', 'inclusion': 1, 'index_key': '`VMs`', 'low': '[]'}]}], 'using': 'gsi'}}, {'#operator': 'Parallel', '~child': {'#operator': 'Sequence', '~children': [
{'#operator': 'Unnest', 'as': 'x', 'expr': 'cover ((`emp1`.`VMs`))', 'filter': '((1 < (`x`.`RAM`)) and ((`x`.`RAM`) < 5) and (`x` is not missing))'}]}},
{'#operator': 'Join', 'as': 'task', 'keyspace': 'default', 'namespace': 'default', 'on_keys': 'cover ((meta(`emp1`).`id`))'}, {'#operator': 'Parallel', '~child': {'#operator': 'Sequence', '~children': [{'#operator': 'InitialProject', 'result_terms': [
{'expr': '`x`'}]}]}}]}