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

N1QL: Array Indexing: Index created without distinct is not being used in same way as created with distinct

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Critical
    • 4.5.0
    • 4.5.0
    • query
    • 4.5.0-1722
    • Untriaged
    • Unknown

    Description

      Indexes created:
      CREATE INDEX `idxjoin_yr` ON `default`(array `v` for `v` in `join_yr` end) WITH(

      {"index_type" : "forestdb"}

      )

      and

      CREATE INDEX `idxjoin_yr2` ON `default`((distinct (array `v` for `v` in `join_yr` end))) WITH(

      {"index_type" : "forestdb"}

      )

      Query issued:
      EXPLAIN select name from default use index (`idxjoin_yr2`) where any v in default.join_yr satisfies v = 2016 END;

      uses the correct index

      while same query with idxjoin_yr:
      EXPLAIN select name from default use index (`idxjoin_yr`) where any v in default.join_yr satisfies v = 2016 END;

      uses primary scan instead of index scan.

      Dataset: Bucket default with documents like:
      {
      "tasks": [
      {
      "Marketing": [

      { "region2": "International", "region1": "South" }

      ,

      { "region2": "South" }

      ],
      "Developer": [
      "IOS",
      "Indexing"
      ]
      },
      "Sales",
      "QA"
      ],
      "name": [

      { "FirstName": "employeefirstname-9" }

      ,

      { "MiddleName": "employeemiddlename-9" }

      ,

      { "LastName": "employeelastname-9" }

      ],
      "address": [
      [

      { "city": "Delhi" }

      ,

      { "street": "12th street" }

      ],
      [

      { "country": "EUROPE", "apartment": 123 }

      ]
      ],
      "email": "9-mail@couchbase.com",
      "mutated": 0,
      "hobbies": {
      "hobby": [

      { "sports": [ "Badminton", "Football", "Basketball" ] }

      ,

      { "dance": [ "hip hop", "bollywood", "contemporary" ] }

      ,
      "art"
      ]
      },
      "department": "Support",
      "join_yr": [
      2013,
      2015,
      2012
      ],
      "_id": "query-testemployee10153.1877827-0",
      "VMs": [

      { "RAM": 10, "os": "ubuntu", "name": "vm_10", "memory": 10 }

      ,

      { "RAM": 10, "os": "windows", "name": "vm_11", "memory": 10 }

      ,

      { "RAM": 10, "os": "centos", "name": "vm_12", "memory": 10 }

      ,

      { "RAM": 10, "os": "macos", "name": "vm_13", "memory": 10 }

      ]
      }

      Attachments

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

        Activity

          People

            keshav Keshav Murthy
            Prerna.Manaktala Prerna Manaktala (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty