I have problems with understanding of way couchbase query plan works. I use SpringData with Couchbase 4.1 and I provide custom implementation of Couchbase Repository. Inside my custom implememtnation of Couchbase Repository I have below method:
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);
In a result, Spring Data produces following json object represented query to 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 problem is with performance when I execute it via Java and N1QL Rest Api or via cbq consol. For execute this query in cbq I simply replace parameters reference with exact values.
After adding EXPLAIN clause before select statement I mentioned different execution plans. Execution this query as parameterized query via Java Spring Data or N1QL Rest Api I've mentioned that query doesn't use index that I created exactly for this case. Index definiton can be found below:
CREATE INDEX `testMembers` ON MyDatabase `m`(`_class`,`segmentId`,`executionTime`) WHERE (`_class` = "path/MyClass") USING GSI;
So, when I execute query via cbq consol, Couchbase uses my idnex and query performance is very good. But, when I execute this query via N1QL rest api or Java i see that query doesn't use my index. Below you can find part of execution plan that proves this fact:
"~children": [
{
"#operator": "PrimaryScan",
"index": "#primary",
"keyspace": "CSM",
"namespace": "default",
"using": "gsi"
},
So, the question is that the right and legal behavior of couchbase query optimizer? And does it mean that query plan does not take into account real values of parameters? And have I manually put values into query string or exist eny other way to use N1Ql parameterized query with correct index selection?
EDIT
According to shashi raj answer I add N1qlParams.build().adhoc(false) parameter to parameterized N1QL query. This doesn't solve my problem, because I still have performance problem with this query. Moreover, when I print query I see that it is the same as I described earlier. So, my query still wrong analyzed and cause performance decline.
The problem in your case is caused by the fact that you have an index with 'where' clause WHERE ( _class = "path/MyClass"), and at the same time, you passing the _class as a parameter in your query.
Thus, the query optimizer analyzing the parametrized query has no idea that this query might use an index created for _class = "path/MyClass", cause it's _class = $class in a select's where. Pretty simple, right?
So, don't pass any fields mentioned in your index's 'where' as select parameters. Instead, hardcode _class = "path/MyClass" in your select in the same way you did for create index. And everything should be fine.
Here's the ticket in the couchbase issue tracking system about that.