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