Description
For this query:
SELECT META().id
|
FROM `bucket`
|
WHERE (ANY e IN arr1 SATISFIES e.c0 = "1111" END |
OR ANY e IN arr2 SATISFIES e.c0 = "1111" END) |
AND c1 = "green" |
AND (c2 = "abc" |
OR c3 = "abc") |
index advisor suggests:
CREATE INDEX adv_c1_c2_c3_DISTINCT_arr1_c0_DISTINCT_arr2_c0 ON `bucket`(`c1`,`c2`,`c3`,DISTINCT ARRAY `e`.`c0` FOR e in `arr1` END,DISTINCT ARRAY `e`.`c0` FOR e in `arr2` END);
|
CREATE INDEX adv_c1_DISTINCT_arr2_c0_c2 ON `bucket`(`c1`,DISTINCT ARRAY `e`.`c0` FOR e in `arr2` END,`c2`);
|
CREATE INDEX adv_c1_c3_DISTINCT_arr2_c0 ON `bucket`(`c1`,`c3`,DISTINCT ARRAY `e`.`c0` FOR e in `arr2` END);
|
The first index is invalid as it has multiple array expressions.
If the array expressions in the query are at the end i.e.
SELECT META().id
|
FROM `bucket`
|
WHERE c1 = "green" |
AND (c2 = "abc" |
OR c3 = "abc") |
AND (ANY e IN arr1 SATISFIES e.c0 = "1111" END |
OR ANY e IN arr2 SATISFIES e.c0 = "1111" END) |
then I get valid indexes from the advisor i.e.
CREATE INDEX adv_c2_c1_DISTINCT_arr1_c0 ON `bucket`(`c2`,`c1`,DISTINCT ARRAY `e`.`c0` FOR e in `arr1` END);
|
CREATE INDEX adv_c1_DISTINCT_arr2_c0_c2 ON `bucket`(`c1`,DISTINCT ARRAY `e`.`c0` FOR e in `arr2` END,`c2`);
|
CREATE INDEX adv_c3_DISTINCT_arr1_c0_c1 ON `bucket`(`c3`,DISTINCT ARRAY `e`.`c0` FOR e in `arr1` END,`c1`);
|
CREATE INDEX adv_DISTINCT_arr2_c0_c1_c3 ON `bucket`(DISTINCT ARRAY `e`.`c0` FOR e in `arr2` END,`c1`,`c3`);
|
Attachments
Issue Links
- relates to
-
MB-60572 Index advisor incorrectly recommend an index with multiple array keys for UNNEST
- Closed
For Gerrit Dashboard: MB-50565 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
170420,2 | MB-50565 Do not generate indexes with multiple array keys | master | query-ee | Status: MERGED | +2 | +1 |
204633,2 | MB-60572 Proper detection of multiple array index keys in index advisor | 7.6.0 | query-ee | Status: MERGED | +2 | +1 |
204704,2 | MB-60572 Proper detection of multiple array index keys in index advisor | neo | query-ee | Status: MERGED | +2 | +1 |