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

index advisor recommends an index that already exists

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: Neo
    • Fix Version/s: Neo
    • Component/s: query
    • Labels:
      None
    • Environment:
      7.1.0-1335
    • Triage:
      Untriaged
    • Story Points:
      1
    • Is this a Regression?:
      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

          Hide
          ajay.bhullar Ajay Bhullar added a comment - - edited

          A similar case in which there are two indexes that are equivalent

          SELECT *
          FROM default
          WHERE country = "Norfolk Island" and email = "Willian.Abshire@hotels.com"
          AND ANY r IN reviews SATISFIES r.author = "Martin Feest" and r.ratings.Cleanliness = 2 END;
          Sometimes, it says indexes are sufficient sometimes it recommends the same index with a different ordering inside of flatten_keys, this is pretty minor, but it can lead to possibly recommending an index that already exists like above

          Index Currently Used
          CREATE INDEX idx3 ON `default`((distinct (array flatten_keys((`r`.`author`), ((`r`.`ratings`).`Cleanliness`)) for `r` in `reviews` end)),`country`,`email`)

          Index Recommendations
          CREATE INDEX adv_DISTINCT_reviews_author_ratings_Cleanliness_country_email ON `default`(DISTINCT ARRAY FLATTEN_KEYS(`r`.`ratings`.`Cleanliness`,`r`.`author`) FOR `r` IN `reviews` END,`country`,`email`)

          Show
          ajay.bhullar Ajay Bhullar added a comment - - edited A similar case in which there are two indexes that are equivalent SELECT * FROM default WHERE country = "Norfolk Island" and email = "Willian.Abshire@hotels.com" AND ANY r IN reviews SATISFIES r.author = "Martin Feest" and r.ratings.Cleanliness = 2 END; Sometimes, it says indexes are sufficient sometimes it recommends the same index with a different ordering inside of flatten_keys, this is pretty minor, but it can lead to possibly recommending an index that already exists like above Index Currently Used CREATE INDEX idx3 ON `default`((distinct (array flatten_keys((`r`.`author`), ((`r`.`ratings`).`Cleanliness`)) for `r` in `reviews` end)),`country`,`email`) Index Recommendations CREATE INDEX adv_DISTINCT_reviews_author_ratings_Cleanliness_country_email ON `default`(DISTINCT ARRAY FLATTEN_KEYS(`r`.`ratings`.`Cleanliness`,`r`.`author`) FOR `r` IN `reviews` END,`country`,`email`)
          Hide
          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited

          All are equality predicates so it randomly recommends. As there is no other way to decide. This is same as MB-41251

          Show
          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited All are equality predicates so it randomly recommends. As there is no other way to decide. This is same as MB-41251
          Hide
          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment -

          Try with next build

          Show
          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - Try with next build
          Hide
          build-team Couchbase Build Team added a comment -

          Build couchbase-server-7.1.0-1348 contains query-ee commit 85bad66 with commit message:
          MB-48582. prioirity same order by keynames

          Show
          build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1348 contains query-ee commit 85bad66 with commit message: MB-48582 . prioirity same order by keynames

            People

            Assignee:
            ajay.bhullar Ajay Bhullar
            Reporter:
            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