Couchbase query execution taking too much time

query

#1

We want fast execution of query on the couchbase that why we are are create a index in the couchbase using query

CREATE INDEX `index_type_userid` ON `bucket_Name`(type, userId) USING GSI;

Trying to get the data though query

Select * from `bucket_Name` where  type='doc_type'and userId='User_Id'

But the problem is that query is taking too much time after creating the index. As I read that creating index is taking less time to execute query.

Any suggestion that how we are going to increase query efficiency in Couchbase?


#2

As I run the query in Explain I get the following response

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "IndexScan2",
          "index": "index_type_user",
          "index_id": "f996a699ea933df4",
          "index_projection": {
            "primary_key": true
          },
          "keyspace": "dev",
          "namespace": "default",
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "\"trip\"",
                  "inclusion": 3,
                  "low": "\"trip\""
                },
                {
                  "high": "\"C2mSk7Lo0sgtFL12FcNXY3LMPVl1\"",
                  "inclusion": 3,
                  "low": "\"C2mSk7Lo0sgtFL12FcNXY3LMPVl1\""
                }
              ]
            }
          ],
          "using": "gsi"
        },
        {
          "#operator": "Fetch",
          "keyspace": "dev",
          "namespace": "default"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "(((`dev`.`type`) = \"trip\") and ((`dev`.`userId`) = \"C2mSk7Lo0sgtFL12FcNXY3LMPVl1\"))"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "self",
                    "star": true
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "Select * from `dev` where  type='trip'and userId='C2mSk7Lo0sgtFL12FcNXY3LMPVl1'"
  }
] 

The index is used but it take time in executing


#3

The plan looks right. How many documents query qualify. What is time it is taken.


#4

When I restart couhbase server is working fine . Is this any dependency that after creating the index you have to restart the couhbase server ?


#5

No. It is not required.