Slow N1QL query while finding email


#1

Hi,

I have written a very simple query to check if certain email exists or not and I have also index email key in the document but query takes more than 200ms. Total record in the bucket is more than 2000k.
Following is the query

SELECT `user_name`,`email`,`mobile` FROM `users` WHERE `email`="mustanish.altamash@gmail.com";

Following is the result of my explain query

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan2",
        "index": "users_email",
        "index_id": "f542e8dc30032b43",
        "index_projection": {
          "primary_key": true
        },
        "keyspace": "users",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"mustanish.altamash@gmail.com\"",
                "inclusion": 3,
                "low": "\"mustanish.altamash@gmail.com\""
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Fetch",
        "keyspace": "users",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "((`users`.`email`) = \"mustanish.altamash@gmail.com\")"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "(`users`.`user_name`)"
                },
                {
                  "expr": "(`users`.`email`)"
                },
                {
                  "expr": "(`users`.`mobile`)"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT `user_name`,`email`,`mobile` FROM `users` WHERE `email`=\"mustanish.altamash@gmail.com\";"
}

#2
The plan looks good. If you need faster use covering index.
CREATE INDEX ix1 ON `users` (`email`, `user_name`,`mobile`);