Details
-
Improvement
-
Resolution: Fixed
-
Critical
-
6.5.0
Description
https://docs.google.com/document/d/1_L3zu7ro5koFkeEHtAr5f9fReuZfNd7QlmOj8x0yidg/edit
In case of array of objects user can only index one field or convert them into array or object and index them. |
|
Index single array field: predicate is on multiple array fields the options are limited and queries perform poorly.
|
|
Create multiple indexes and query does Intersect Scans. No covering indexes. Number of indexes increases, Indexer need more memory and due to intersect scan queries can perform poorly and less throughput. Use single index and filter post index scan. All predicates not pushed, disables other pushdowns and higher KV fetch rate and impacts performance and throughput.
|
|
Convert into Array or object: You can index as array of fields or object. This loses flexibility and may not able to apply predicate individually due to that loses functionality.
|
|
Array or object comparison is whole. Mostly can use equality. Not able to do any other comparisons |
|
In array index key if more than one field is present flatten them and index as if reset of the index keys are moved to right by that many positions. Queries will be sargable accordingly and generate spans. |
|
FLATTEN/UNNEST/UNWIND DISTINCT ARRAY f(x) FOR x IN expr1 END;
|
|
f(x) must be Array Construct (i.e [v1.fname,v1.lname]). ARRAY expression is used many places and f(x) must be single expression. We take as array construct and do flatten based on modifier after DISTINCT. As far as query is concerned no syntax change but it needs to be properly sargable and spans needs to be generated. It should support nested syntax and only FLATTEN, Array Construct allowed on innermost one. |
|
Example 1: |
|
CREATE INDEX ix1 ON default(c0, c1, DISTINCT ARRAY FLATTEN_KEYS(v1.fname DESC, v1.lname ASC) FOR v1 IN a1 END, c2,c3); |
====> (c0,c1,v1.fname DESC ,v1.lname ASC ,c2,c3);
|
|
SELECT META().id
|
FROM default |
WHERE c0 > 0 AND |
c1 >= "a" AND |
ANY v1 IN a1 SATISFIES v1.fname = "mark" AND v1.lname > "O" END; |
|
Spans :
|
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"inclusion": 0, |
"low": "0" |
},
|
{
|
"inclusion": 1, |
"low": "\"a\"" |
},
|
{
|
"high": "\"mark\"", |
"inclusion": 3, |
"low": "\"mark\"" |
},
|
{
|
"inclusion": 0, |
"low": "\"O\"" |
}
|
]
|
}
|
]
|
|
ID | Requirement | Comments | |
Introduce new modifier for Array Index key. ( or UNWIND or UNNEST). Mark it is reserved keyword. |
|||
Indexer needs to detect this modifier and flatten the array and shift the index keys while indexing | |||
The positions of index keys will be shifted accordingly (in the spans/group/aggregates) | |||
Sargable the index based on the individual flatten keys | |||
Generate spans based on individual flatten keys | |||
Support for both DISTINCT ALL, ALL modifiers | |||
UnnestScan should support new index keys. |
|
||
UNNEST and non-unnest IndexScan combine and do index Selection to avoid IntersectScan |
Attachments
Issue Links
- depends on
-
MB-40583 Array flattening support in GSI
- Closed
- relates to
-
MB-36792 [N1QL][RQG ARRAYS] covered array query is returning incorrect results
- Resolved
-
DOC-9493 index composite index (MB-32506)
- Resolved
-
CMOS-239 Add a check to see if the cluster can create an index on composite array fields
- Done
- links to
(22 links to)