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

composite indexes: expected index is not used

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Critical
    • 4.0.0
    • 4.0.0
    • query
    • Security Level: Public
    • None
    • Untriaged
    • Unknown

    Description

      how user can know what index will be used?
      I have items like
      {
      "tasks_points":

      { "task1": 1, "task2": 1 }

      ,
      "name": "employee-20",
      "mutated": 0,
      "skills": [
      "skill2010",
      "skill2011"
      ],
      "join_day": 20,
      "join_mo": 5,
      "email": "20-mail@couchbase.com",
      "test_rate": 5.5,
      "join_yr": 2010,
      "_id": "query-test003d2ed-0",
      "VMs": [

      { "RAM": 5, "os": "ubuntu", "name": "vm_5", "memory": 5 }

      ,

      { "RAM": 5, "os": "windows", "name": "vm_6", "memory": 5 }

      ],
      "job_title": "Sales"
      }

      and 2 indexes like:
      cbq> select * from system:indexes where "`join_yr`" IN index_key;
      {
      "requestID": "9283861d-b0e9-4e87-9ab8-e3b705911161",
      "signature":

      { "*": "*" }

      ,
      "results": [
      {
      "indexes":

      { "datastore_id": "http://127.0.0.1:8091", "id": "c0522d4194ddcd93", "index_key": [ "`join_yr`" ], "keyspace_id": "default", "name": "ind11", "namespace_id": "default", "state": "online", "using": "gsi" }

      },
      {
      "indexes":

      { "datastore_id": "http://127.0.0.1:8091", "id": "f87e6abe173f5de", "index_key": [ "`join_yr`", "`VMs`" ], "keyspace_id": "default", "name": "ind19", "namespace_id": "default", "state": "online", "using": "gsi" }

      }
      ],
      "status": "success",
      "metrics":

      { "elapsedTime": "12.662550022s", "executionTime": "12.662423452s", "resultCount": 2, "resultSize": 858 }

      }

      cbq>

      the query "select join_yr, VMs from default where join_yr>2 and VMs is not null" has to verify join_yr and VMs.
      I would expect that ind19 will be used, because it has both fields. But now ind11 is used which has only one of the fields.
      Index for second field also exists.
      {
      "indexes":

      { "datastore_id": "http://127.0.0.1:8091", "id": "d62959e45d1f1542", "index_key": [ "`VMs`" ], "keyspace_id": "default", "name": "ind13", "namespace_id": "default", "state": "online", "using": "gsi" }

      },

      Attachments

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

        Activity

          People

            gerald Gerald Sangudi (Inactive)
            iryna iryna
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              PagerDuty