Couchbase uses wrong indexes with N1QL parameterized queries

Question

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.

And apparently, there is nothing to fix :). See my answer below. — gaperton, Sep 22 '17 at 19:49

score 2 · Answer 1 · edited Jan 21 '17 at 05:20

2

first of all you need to know how N1QL parameterized queries works query should be passed as:

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

Now the query should be passed as:

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

where jsonObject will have all the placeholder values.

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

N1qlParams.build().adhoc(false) is optional since if you want your query to be optimized it will make use of it. It makes use of LRU where it keeps track of previously query entered and it keeps record of it so that next time it doesn't need to parse query and fetch it from previous what we call as prepared statement.

The only problem is couchbase only keeps record of last 5000 queried.

edited Jan 21 '17 at 05:20

Benjamin Lowry

3,730
1
23
27

answered Jan 21 '17 at 04:24

shashi raj

15
3

Sorry, but this didn't solve my problem. I still have performance problems even If I create query with this flag set on false. Maybe do You have any other suggestions? – 4the3eam Jan 26 '17 at 10:27
Try using covering indexes, it solves most of the performance issues. Suppose if u want to query using 5 attributes , specify those attributes while creating the index – shashi raj Jan 26 '17 at 14:27
Please make sure the maaping of variable in the query n Json object should be same. Otherwise it doesn't work . – shashi raj Jan 26 '17 at 14:29
I'm using covering indexes, and even created index on ARRAY_COUNT(myDatabase.Array) function. Also, when I manually pass parameter values to query I see that query analyzer uses right index. – 4the3eam Jan 28 '17 at 14:29
And when I added N1QLParams.build().adhoc(false) to query builder, I see the same query. I thought that when I add this parater to query builder Java will automatically pass values of these parameters into "statement" clause in N1QL query json object. Because as I understand responsible for query optimization processes is internal Couchbase engine rather than Java, so I assumed that query builder must pass values into query string. – 4the3eam Jan 28 '17 at 14:35

gaperton · Accepted Answer · 2017-09-22T19:48:29.663

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.

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

Couchbase uses wrong indexes with N1QL parameterized queries

2 Answers2