Couchbase doesn't use index with filter when execute parametized N1QL query

Question

I had created global secondary index with where condition in my Couchbase cluster:

CREATE INDEX commentAuthorSecondaryIndex ON Bucket(author) WHERE (_class = "com.company.Comment") USING GSI;

then I tried execute two similar queries via N1QL (REST API):

curl --header "Content-Type:application/json" http://localhost:8093/query/service -d '
{
  "$class" : "com.company.Comment",
  "statement" : "select count(*) from Bucket WHERE _class = $class and author = $author",
  "$author" : "author455",
  "scan_consistency" : "statement_plus"
}'

and :

curl --header "Content-Type:application/json" http://localhost:8093/query/service -d '
{  
   "statement" : "select count(*) from Bucket WHERE _class = \"com.company.Comment\" and author = $author",
   "$author" : "author455",
   "scan_consistency" : "statement_plus"
}

First query was executed fast 4 seconds while second took only 20 miliseconds. When I used explain keyword I realized that in first query Couchbase doesn't use index.

I suppose that Couchbase firstly create query execution plan and only then evaluate query with parameters. So when query plan is created Couchbase doesn't know that index commentAuthorSecondaryIndex can be used.

Is any way to fix this?

Two options--remove the filter from the index, or do not make _class a parameter in the your query. See forums.couchbase.com/t/… — geraldss
Thank you. It's clear now. Are there any plans to fix this in the future? — IgorekPotworek

geraldss geraldss · Accepted Answer · 2017-02-08T17:23:38

Two options--remove the filter from the index, or do not make _class a parameter in the your query. See https://forums.couchbase.com/t/whats-the-best-approach-to-prevent-sql-injection-with-n1ql/11636/8

Couchbase doesn't use index with filter when execute parametized N1QL query

1 Answers