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

LIKE vs. = yields different index decision

    XMLWordPrintable

Details

    • Untriaged
    • MacOSX 64-bit
    • 0
    • Unknown

    Description

      Playing with CBO on beer-sample, after doing:

           ANALYZE ANALYTICS COLLECTION _default;

      I noticed that:

           SELECT * FROM _default WHERE city = 'San Diego';

      uses a city index if you create one:

           CREATE INDEX city_index ON _default(city: string);

      However, the following seemingly equivalent query doesn't:

           SELECT * FROM _default WHERE city LIKE 'San Diego';

      Interestingly, the selectivity estimate for both queries is identical but the cost estimates and decision are not.  For the equality case, the top of the plan says:

             "optimizer-estimates":

      {            "cardinality": 0.73,            "op-cost": 0,            "total-cost": 2.59        }

      vs. what it says for the LIKE case:

          "optimizer-estimates":

      {            "cardinality": 0.73,            "op-cost": 0,            "total-cost": 7336.16     }

      The index should be picked, one would think?

      This is not CBO's fault, it seems, as if you disable CBO the behavior is the same.  I confirmed that the candidate AsterixDB release shares this issue.

      We should use the index in this case!  (Both in RBO and in CBO.)

       

       

      Attachments

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

        Activity

          People

            murali.krishna Murali Krishna
            mike.carey Mike Carey
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty