N1QL Query Optimizer issue

Couchbase Server
1

Hi, all,i’ve found an issue with the index selection on cb.
Description:
The optimizer does not always choose and index if a partial match is found between the selection predicate and one index definition that can partially satisfy (only through the filter index definition) the where predicate when the where predicate consists only as an equality constraint on an expression matching the index filter condition.

steps to reproduce: giving a set of documents in a bucket, with this structure:
{
a:1,b:2, c:3, type:‘Triplet’
}
and an index definition like
create index choosemeplease on development (development.a) where development.type=‘Triplet’

issuing the following query:
select c.* from development c where c.type = ‘Triplet’ and c.a >= 0 i expect the optmizer to choose the index above.
the query plan generated show that a full bucket scan will be done.

however the optimizer choose the correct index if i change the query in:
select c.* from development c where c.type = ‘Triplet’ and c.a >= 0
in the latter case there is no issue.
I suspect that when a partial expression match is found the index filter condition is not fully take into account.

2

Your two queries are identical.

If I were to guess, you should drop your index and create the following index.

create index choosemeplease on development (a) where type='Triplet'

3

i apologize, a copy paste problem. the first query lacks the leading “and c.a >=0” clause.
this is the query i think it’s incorrectly optimized.
sorry i cannot figure where the index you suggested me to create is different from the first one i had

4

In that case, the optimizer is doing the right thing.

You can use

c.a IS NOT MISSING

if you want to use the index.

5

umh, i expected that the optimizer choose the less expesive way . in my bucket i’ve had a lot of documents and about 100 docs with type=‘Triplet’. with the latter query it does a full scan of the primary index, it does fetch all the documents in the bucket about 10^6 to fetch only 100 documents of type triplet…

6

ah, i’ve got it now. my relational background ‘fooled’ me in judging. sorry.

7

We are all forgiven.

:smile: