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.
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.
|For Gerrit Dashboard: MB-32366|
|103054,4||MB-32366 Allow index span on non-leading index keys for UNNEST scan||alice||query||Status: MERGED||+2||+1|
|103260,2||Backport index unnest tests for MB-32366.||alice||testrunner||Status: MERGED||+2||+1|