Inconsistent Query Timing

Couchbase Server
,
1

JSON Schema -

{
“alphabets”: “ABC|DEF|HIJ”,
“field1”: “NA”,
“field2”: “NA”,
“category”:“trip”,
“doc_type”:“email”
}

CREATE INDEX idx_new ON <bucket_name>(split(lower(alphabets), “|”)) WITH { “nodes”:[ <2 index nodes> ], “num_replica”:1 }

Query-
select t.entry_id
from <bucket_name> as t
where ARRAY_LENGTH(ARRAY_INTERSECT(SPLIT(LOWER(t.alphabets),‘|’),[‘abc’])) > 0
AND t.category= ‘impulse’
AND t.doc_type = ‘email’ limit 20 offset 0

We do have indexes for other fields as well, but when we try query wth alphabets as ABC, it takes 3 secs and when we try with DEF it takes almost a minute. Any help will be much appreciated.

2 
CREATE INDEX  `idx_new`  ON  `<bucket_name>`
 (category, DISTINCT ARRAY LOWER(v) FOR v  IN SPLIT( alphabets, "|") END)  WHERE doc_type = "email";

SELECT t.entry_id
FROM   `<bucket_name>`  as t
WHERE   ANY v IN  SPLIT( t.alphabets, "|") SATISFIES LOWER(v) = "abc" END
AND t.category= "impulse"
AND t.doc_type = "email"
LIMIT 20 
OFFSET 0

If possible use covering index.

3

Thanks for the reply @vsr1 .

We have multiple fields to choose from (exactly 8 fields). For ex:

  1. User can query for doc_type where field1 = NA and field2 = NA
  2. User can query for field2 = NA
  3. User can query for field2 = NA and alphabets = [‘abc’, ‘xyz’, ‘pqr’] (i.e. any of the alphabets should be present)

Considering this scenario we followed index on each field separately instead of a covering index, which follows the predicate matching. Correct me if, going in wrong direction.

4

If you need permutations of 8 fields you may consider FTS