Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-48582

index advisor recommends an index that already exists

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Minor
    • 7.1.0
    • 7.1.0
    • query
    • None
    • 7.1.0-1335
    • Untriaged
    • 1
    • No

    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`)

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          People

            ajay.bhullar Ajay Bhullar
            ajay.bhullar Ajay Bhullar
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty