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

Index condition has != suboptimal index is choosen

    XMLWordPrintable

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Won't Fix
    • 6.6.1, 6.6.2
    • None
    • query
    • Untriaged
    • 1
    • Unknown

    Description

      DROP INDEX default.ix10;
      		 
      DROP INDEX default.ix11;
      		 
      CREATE INDEX ix10 ON default(a,b,c) WHERE type != "_sync:";
      		 
      CREATE INDEX ix11 ON default(a,b1) WHERE type != "_sync:";
      		 
      EXPLAIN SELECT * FROM default WHERE a = 1 AND c = 5 AND type != "_sync:";

      In 6.6.x it chooses ix11 and not pushing c==5 to indexer.
      It works in 7.0.0

      Attachments

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

        Activity

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment -

          As part of the MB-31203

          Index selection is based on continuous keys only in 6.6.x. (i.e. same as pre 6.5.0)
          Make Index selection is based on continuous keys only and don't include skip keys.
          Once Index selection is made skip keys are included in span generation.

          This decision is made to fix regressions. This has been addressed in 7.0 and backport is too risky.

          Query can add another predicate or USE INDEX is alternative.

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - As part of the MB-31203 Index selection is based on continuous keys only in 6.6.x. (i.e. same as pre 6.5.0) Make Index selection is based on continuous keys only and don't include skip keys. Once Index selection is made skip keys are included in span generation. This decision is made to fix regressions. This has been addressed in 7.0 and backport is too risky. Query can add another predicate or USE INDEX is alternative.

          People

            kamini.jagtiani Kamini Jagtiani (Inactive)
            Sitaram.Vemulapalli Sitaram Vemulapalli
            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