Java parameterized N1Ql queries is not using Indices

SQL++
1

Hello,

I am using Java client 2.4.5, couchbase 5.0 Beta build.

My parameterized N1Ql statements are not using GSI Indexes as applicable.

When I run the same query in Couchbase console, the execution plan is using right indices and the response is sub millisecond

When I do the same using Java SDK parameterized N1QL, I am not sure what is going wrong, the query response Is in seconds

please help.

Besides, IS there a way I can display execution plan of my parameterized N1ql statements running in my java application

Regards
Shiv

2

Post the index definition, query and parameters.

  1. If Index contains where clause and query where clause that match index condition needs to be static not parameterized.
  2. If Parameter passing is array and it right side of IN clause( x1 IN $param1), the query needs to retrieve all values of x1 before applying predicate. To make it faster generate query text by replacing parameter with actual values and do adhoc query.
1 Like
3

Ok. That helps.

My Issues falls under condition #1.

By making the params static (only the where clause index condition), parameterized N1ql statements consider Indices

4

For non prepared statements this will be improved in upcoming releases https://issues.couchbase.com/browse/MB-24871

5

Hi,
Is this issue resolved on Couchbase Enterprise 5.5.3 / Java SDK 2.7.0?
I have the same problem this days. Then I use parameterized N1QL from Java SDK, he ignores my covered indexes, but works then I use simple N1QL.

My problem is the first scenario @vsr1 mentions: “If Index contains where clause and query where clause that match index condition needs to be static not parameterized.”

example:
My coreverd index: CREATE INDEX idx_emails on CONTENT(LOWER(email)) where provider = “Google” and country=“US”

Wrong index used: CREATE INDEX idx_provider on CONTENT(country, provider)
Query:
Select * from CONTENT where LOWER(email) = $1 and provider = $2 and country = $3
params: $1 ="random@gmail.com", $2=“Google”, $3=“US”

6

Query plan generated during the prepare time and parameters are only available during the execution.
For Partial index selection Index WHERE clause part of the query must be static (no query parameters).

Example: If query during prepare time it uses idx_emails and execution If you pass $3 = “France”, index will not have entries for “France” and returns wrong results. Due to that reason idx_emails will not qualify. That is why idx_provider is used.

You have the following options:

  1. Change portion of query that matches index where clause such that it uses static values (no parameters)
  2. Don’t use prepared statements i.e. set adhoc=true
  3. Don’t create partial index
    CREATE INDEX idx_provider on CONTENT(country, provider, LOWER(email) );

https://index-advisor.couchbase.com/indexadvisor/#1

7

Thanks,
I understand, For my case, its crucial to use partial indexes, so option 1 is my best.