Description
I have this query being served by this index
CREATE INDEX `idx2` ON `default`((distinct (array flatten_keys((`r`.`author`)) for `r` in `reviews` end)),`country`,`email`)
SELECT *
FROM default
WHERE country = "Norfolk Island" and email = "Willian.Abshire@hotels.com"
AND ANY r IN reviews SATISFIES r.author = "Martin Feest" END;
When I run an advise it will advise this index
CREATE INDEX `adv_DISTINCT_reviews_author_country_email` ON `default`((distinct (array (`r`.`author`) for `r` in `reviews` end)),`country`,`email`)
If I build this index and run the advise again, sometimes it recommends building the same index that already exists and sometimes it says the indexes being used are sufficient, this is probably because the underlying query will sometimes use the flatten_keys index and sometimes use the new index interchangeably. Should it even advise an equivalent index? This seems to be an edge case due to there being a flatten keys on only one field.
Index Currently Used
CREATE INDEX idx2 ON `default`((distinct (array flatten_keys((`r`.`author`)) for `r` in `reviews` end)),`country`,`email`)
Index Recommendations
CREATE INDEX adv_DISTINCT_reviews_author_country_email ON `default`(DISTINCT ARRAY `r`.`author` FOR `r` IN `reviews` END,`country`,`email`)