[4.6.2] Index give the wrong query result

Couchbase Server
1

Hi,
I got difference result when perform same query with and without index. This is the query:

select dangBoi.email from default where type=‘Ads’ and dangBoi.email = ‘nguyenbavuongcuong@gmail.com’

  1. First, when having index ads_user_idx03:
    CREATE INDEX ads_user_idx03 ON default((dangBoi.email)) WHERE (type = “Ads”)
    It will return 2 result:

[{
“email”: “nguyenbavuongcuong@gmail.com”
},
{
“email”: “nguyenbavuongcuong@gmail.com”
}]

  1. Then I dron the index “ads_user_idx03”:
    The query gave me 0 results

Can you advice ?

2

Please post EXPLAIN with and without Index.

Also try see which keys those are and query those keys using USE KEYS. Indexer may be little behind and may not have caught up any delete.

select META().id, dangBoi.email from default where type=‘Ads’ and dangBoi.email = ‘nguyenbavuongcuong@gmail.com’

3

1) Wth index:

1.1 N1QL:

select dangBoi.email, META().id from default where type=‘Ads’ and dangBoi.email = ‘nguyenbavuongcuong@gmail.com’

Result:

{
“email”: “nguyenbavuongcuong@gmail.com”,
“id”: “Ads_01_12266490”
},
{
“email”: “nguyenbavuongcuong@gmail.com”,
“id”: “Ads_01_12337728”
}

Explain:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "IndexScan",
          "covers": [
            "cover (((`default`.`dangBoi`).`email`))",
            "cover ((meta(`default`).`id`))"
          ],
          "filter_covers": {
            "cover ((`default`.`type`))": "Ads"
          },
          "index": "ads_user_idx03",
          "index_id": "bc899a72f133f346",
          "keyspace": "default",
          "namespace": "default",
          "spans": [
            {
              "Exact": true,
              "Range": {
                "High": [
                  "\"nguyenbavuongcuong@gmail.com\""
                ],
                "Inclusion": 3,
                "Low": [
                  "\"nguyenbavuongcuong@gmail.com\""
                ]
              }
            }
          ],
          "using": "gsi"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "((cover ((`default`.`type`)) = \"Ads\") and (cover (((`default`.`dangBoi`).`email`)) = \"nguyenbavuongcuong@gmail.com\"))"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "cover (((`default`.`dangBoi`).`email`))"
                  },
                  {
                    "expr": "cover ((meta(`default`).`id`))"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "select dangBoi.email, META().id from default where type='Ads' and dangBoi.email = 'nguyenbavuongcuong@gmail.com'"
  }
]

1.2 N1QL:

select dangBoi.email, META() from default where type=‘Ads’ and dangBoi.email = ‘nguyenbavuongcuong@gmail.com’

Result: 0 results

Explain:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "IndexScan",
          "index": "ads_user_idx03",
          "index_id": "bc899a72f133f346",
          "keyspace": "default",
          "namespace": "default",
          "spans": [
            {
              "Exact": true,
              "Range": {
                "High": [
                  "\"nguyenbavuongcuong@gmail.com\""
                ],
                "Inclusion": 3,
                "Low": [
                  "\"nguyenbavuongcuong@gmail.com\""
                ]
              }
            }
          ],
          "using": "gsi"
        },
        {
          "#operator": "Fetch",
          "keyspace": "default",
          "namespace": "default"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "(((`default`.`type`) = \"Ads\") and (((`default`.`dangBoi`).`email`) = \"nguyenbavuongcuong@gmail.com\"))"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "((`default`.`dangBoi`).`email`)"
                  },
                  {
                    "expr": "meta(`default`)"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "select dangBoi.email, META() from default where type='Ads' and dangBoi.email = 'nguyenbavuongcuong@gmail.com'"
  }
]

1.3 N1QL:

select * from default where type=‘Ads’ and dangBoi.email = ‘nguyenbavuongcuong@gmail.com’

Result: 0 results

2) Without index

2.1 N1QL:

select dangBoi.email from default where type=‘Ads’ and dangBoi.email = ‘nguyenbavuongcuong@gmail.com’

Result: 0 results
Explain:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "IndexScan",
          "index": "type_idx",
          "index_id": "8ddddd313f65538f",
          "keyspace": "default",
          "namespace": "default",
          "spans": [
            {
              "Exact": true,
              "Range": {
                "High": [
                  "\"Ads\""
                ],
                "Inclusion": 3,
                "Low": [
                  "\"Ads\""
                ]
              }
            }
          ],
          "using": "gsi"
        },
        {
          "#operator": "Fetch",
          "keyspace": "default",
          "namespace": "default"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "(((`default`.`type`) = \"Ads\") and (((`default`.`dangBoi`).`email`) = \"nguyenbavuongcuong@gmail.com\"))"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "((`default`.`dangBoi`).`email`)"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "select dangBoi.email from default where type='Ads' and dangBoi.email = 'nguyenbavuongcuong@gmail.com'"
  }
]
4

Do take notes, I got no issue from “indexer.log” when creating “ads_user_idx03” index

5

When covered index gives the results. When Fetched the document it did not has document. Did you delete the documents. If you deleted Indexer may not have updated. @deepkaran.salooja

SELECT * FROM default USE KEYS[ “Ads_01_12266490”,“Ads_01_12337728”];

6

@tinygipxy, you can use REQUEST_PLUS consistency to make sure Index is caught up with the data.

Also you can look up the UI Statistics mini-graphs for the index and see if there are any pending items.

7

It’s ok now, seem the Index is not caught up with the data.
Thanks

8

@tinygipxy, it would be a good idea to increase memory quota for index service(UI->Settings) so it is better caught up with data. You can also try out Memory Optimized Indexes for much improved performance.

9

thanks deepkaran for your sugest