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

Aggregate (count) function forces query to use primary index only (even where there is a 2i index on the field in the group by clause

    XMLWordPrintable

Details

    • Improvement
    • Resolution: Done
    • Critical
    • 4.0.0
    • 4.0.0
    • query
    • Security Level: Public
    • 3 data node, 1 query node, 1 index node. 20M documents, one 2i index on key (email)

    Description

      cbq> select count, capped_small from `bucket-1` group by capped_small;
      {
      "requestID": "e6071bb0-de55-48a0-9ff2-d13b83a6205c",
      "errors": [

      { "code": 4000, "msg": "No primary index on keyspace bucket-1. Use CREATE PRIMARY INDEX to create one." }

      ],
      "status": "fatal",
      "metrics":

      { "elapsedTime": "1.720223ms", "executionTime": "1.335946ms", "resultCount": 0, "resultSize": 0, "errorCount": 1 }

      }
      cbq> explain select count, capped_small from `bucket-1` group by capped_small;
      {
      "requestID": "69078f8b-0127-4623-9772-6cdb0ec84f7a",
      "errors": [

      { "code": 4000, "msg": "No primary index on keyspace bucket-1. Use CREATE PRIMARY INDEX to create one." }

      ],
      "status": "fatal",
      "metrics":

      { "elapsedTime": "1.936496ms", "executionTime": "1.577497ms", "resultCount": 0, "resultSize": 0, "errorCount": 1 }

      }
      cbq> select * from system:indexes;
      {
      "requestID": "517b4cec-3909-45c1-b968-005f0ed65532",
      "signature":

      { "*": "*" }

      ,
      "results": [
      {
      "indexes":

      { "datastore_id": "http://127.0.0.1:8091", "id": "4c757db390196977", "index_key": [ "`capped_small`" ], "keyspace_id": "bucket-1", "name": "by_capped_small", "namespace_id": "default", "state": "online", "using": "gsi" }

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

      { "elapsedTime": "257.405389ms", "executionTime": "257.017194ms", "resultCount": 1, "resultSize": 431 }

      }

      any user would be confused with the error message stating a requirement for a primary index when they use an aggregate function, INCLUDING, explain on a statement including the aggregate function.

      Attachments

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

        Activity

          People

            gerald Gerald Sangudi (Inactive)
            thomas Thomas Anderson (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty