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

Index Advisor makes duplicate index recommendations even with two queries as input

    XMLWordPrintable

Details

    • Bug
    • Resolution: Not a Bug
    • Major
    • 7.0.0
    • Cheshire-Cat
    • query
    • 1

    Description

      Here is the reproduction using optim_focus_suites.

      SELECT ADVISOR(["SELECT MAX(x.u016_n) FROM KS2 x JOIN KS1 y ON x.u04k_i = y.u04k_i WHERE x.norm_n <= 37212 AND y.norm_n <= 37212 GROUP BY x.u256_i ORDER BY x.u256_i LIMIT 25",
                                  "SELECT MAX(x.u016_n) FROM KS1 x JOIN KS2 y ON x.u04k_i = y.u04k_i WHERE x.norm_n <= 33545 AND y.norm_n <= 33545 GROUP BY x.u256_i ORDER BY x.u256_i LIMIT 25"]) AS Multiple;

       

       

      These are the CREATES it generates.  

      "index": "CREATE INDEX adv_norm_n_u04k_i ON `KS1`(`norm_n`,`u04k_i`)",
       "index": "CREATE INDEX adv_norm_n_u04k_i ON `KS1`(`norm_n`,`u04k_i`)",                  

      "index": "CREATE INDEX adv_norm_n_u04k_i ON `KS2`(`norm_n`,`u04k_i`)",

      "index": "CREATE INDEX adv_norm_n_u04k_i ON `KS2`(`norm_n`,`u04k_i`)",             

             "index": "CREATE INDEX adv_norm_n_u04k_i_u256_i_u016_n ON `KS1`(`norm_n`,`u04k_i`,`u256_i`,`u016_n`)",
                          "index": "CREATE INDEX adv_norm_n_u04k_i_u256_i_u016_n ON `KS2`(`norm_n`,`u04k_i`,`u256_i`,`u016_n`)",

      Attachments

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

        Activity

          People

            pierre.regazzoni Pierre Regazzoni
            murali.krishna Murali Krishna
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty