Couchbase uses invalid indexes with parameterized N1QL queries

Question

I have problems understanding how couchbase query plan works. I use SpringData with Couchbase 4.1, and I provide my own implementation of the Couchbase repository. Inside my custom implementation of the Couchbase repository, I have a method below:

String queryAsString = "SELECT MyDatabase.*, META().id as _ID, META().cas as _CAS FROM MyDatabase WHERE segmentId = $id AND _class = $class ORDER BY executionTime DESC LIMIT 1";
JsonObject params = JsonObject.create()
        .put(CLASS_VARIABLE, MyClass.class.getCanonicalName())
        .put(ID_VARIABLE, segmentId);

N1qlQuery query = N1qlQuery.parameterized(queryAsString, params);
List<MyClass> resultList = couchbaseTemplate.findByN1QL(query, SegmentMembers.class);
return resultList.isEmpty() ? null : resultList.get(0);

As a result, Spring Data creates the following json object presented in Couchbase:

{
    "$class":"path/MyClass",
    "statement":"SELECT MyDatabase.*, META().id as _ID, META().cas as _CAS from  MyDatabase where segmentId = $id AND _class = $class ORDER BY executionTime DESC LIMIT 1",
    "id":"6592c16a-c8ae-4a74-bc17-7e18bf73b3f8"
}

And the performance problem is when I execute it through Java and N1QL Rest Api or through the cbq console. To execute this query in cbq, I simply replace the parameter references with exact values.

EXPLAIN select . Java Spring Data N1QL Rest Api. , , . :

CREATE INDEX `testMembers` ON MyDatabase `m`(`_class`,`segmentId`,`executionTime`) WHERE (`_class` = "path/MyClass") USING GSI;

, cbq, Couchbase idnex . N1QL rest api Java, , . , :

"~children": [
{
  "#operator": "PrimaryScan",
  "index": "#primary",
  "keyspace": "CSM",
  "namespace": "default",
  "using": "gsi"
},

, , couchbase? , ? N1Ql ?

shashi raj N1qlParams.build(). adhoc (false) N1QL. , . , , , , . , .

Answer 1

, "where" WHERE ( _class = "path/MyClass") _class .

, , , , , _class = "path/MyClass", _class = $class select where. , ?

, , "where" . , hardcode _class = "path/MyClass" , create index. .

couchbase .

https://issues.couchbase.com/browse/MB-22185?jql=text%20~%20%22parameters%20does%20not%20use%20index%22

Answer 2

, , N1QL, :

String query=  select * from bucketName where _class=$_class and segmentId=$segmentId LIMIT $limit ;

:

N1QL.parameterized(query,jsonObject,N1qlParams.build().adhoc(false));

jsonObject placeholder.

JsonObject jsonObject=JsonObject.create().put("_class","com.entity,user").put("segmentId","12345").put("limit",100);

N1qlParams.build().adhoc(false) , , , . LRU, , , .

, couchbase 5000 .