Using OR state between two SATISFIES clauses breaks index selection

SQL++
1

CB 6.5 I have a query that can’t find the index recommended by the advise button when I use an OR clause

Here is my indesx (per the advise button)
CREATE INDEX adv_DISTINCT_tokens_type ON app(DISTINCT ARRAY t FOR t in tokens END) WHERE type = ‘chargeDscrp’

Here is the query:
SELECT *
FROM app
WHERE
type = ‘chargeDscrp’
and
ANY t IN tokens SATISFIES t IN [‘CRANIOFIX’] END
OR ANY t IN tokens SATISFIES t IN [‘CLAMP’,‘FIX’] END

If I swith that OR to and AND it finds and uses that query.
Is this expected? Is there an index that will work with the OR version?

2

@drGarbinsky1,

Index is partial index and index where clause must apply all part of the predicates.
You are missing order of precedence,Adding parenthesis below solves issue. Recommend second option

SELECT *
FROM app
WHERE type = "chargeDscrp"
 AND (ANY t IN tokens SATISFIES t IN ["CRANIOFIX"] END
                OR  ANY t IN tokens SATISFIES t IN ["CLAMP","FIX"] END);

OR

SELECT *
FROM app
WHERE type = "chargeDscrp"
 AND ANY t IN tokens SATISFIES t IN ["CRANIOFIX",  "CLAMP", "FIX"] END;

OR

SELECT *
FROM app
WHERE type = "chargeDscrp"
 AND ANY t IN tokens SATISFIES t IN ["CRANIOFIX"] OR t IN[ "CLAMP", "FIX"] END;
3

That was it! thanks. 40ms across 13 million index entries

4

Would all of this token searching be better served by FTS?

5

@drGarbinsky1 , FTS uses an inverted index under the hood and this kind of an index does benefit querying over arrays.
Couchbase’s FTS supports several kinds of tokenizations and token filtering as well that you might be interested in.