CLONE [MB-30344]- Allow index span on non-leading index keys for unnest scan

Currently UNNEST scan is considered when no other index scan is feasible. UNNEST scan only uses an index if an appropriate array index key is the leading key of an index definition, and it currently only uses predicates on the array index key (leading key). This enhancement allows UNNEST scan to generate index spans on non-leading keys (non-array-index keys) if appropriate predicates exist on those keys.

For example:
 

CREATE INDEX ix30 ON default(ALL ARRAY (ALL ARRAY [op.name, op.val, x] FOR op IN OBJECT_PAIRS(x.properties) END) FOR x IN OBJECT_VALUES(entity.entityVersions) END, accountId, entity.entityId );

SELECT RAW fr

FROM default AS d UNNEST OBJECT_VALUES(d.entity.entityVersions) AS x

     UNNEST OBJECT_PAIRS(x.properties) AS op

LET fr = [op.name, op.val, x][2]

WHERE d.accountId = 2 AND d.entity.entityId = 30002

  AND [op.name,op.val,x] >= ["type2","bla"]

  AND [op.name,op.val,x] < ["type2",SUCCESSOR("bla")];

 
Here we have accountId and entity.entityId as additional index keys after the array index key, and the query has predicates on these predicates. If UNNEST scan is used, we should be able to generate index spans on non-leading these index keys.

This allows the following:

• Reduce false positives during index scan
• Allows other query pushdowns
• Currently no way we can create partition index on individual elements of ARRAY. Only option is include other scalars and create partition index on those. As this enhancement allows those spans push to indexer, indexer can do partition elimination (without spans, it has to scatter gather). This can result in low latencies and cluster scalability.