Create Secondary Index on Array

SQL++
2

The following is right index and query.

CREATE INDEX idx3 ON default (client,feedback_score_sentiment,task_owner_name,DISTINCT ARRAY v.id FOR v IN avatar END, event_time);

SELECT COUNT(1) FROM default AS d
WHERE d.event_time between "2017-05-17T00:00:00.000Z" and "2017-05-20T00:00:00.000Z" AND
d.client= "importtest" AND
ANY v IN d.avatar SATISFIES v.id IN [89000] END AND
ANY s IN d.source SATISFIES s.id IN [100140] END AND
d.feedback_score_sentiment IN ["unscored"] AND
d.task_owner_name IS NULL;
  1. Index can have single array index key. You can choose which one is more selective and use that (avatar or source )
  2. You don’t want event_time in Index WHERE condition because your query has more restricted filter and would like to have this as index key
  3. Array index key variable in this case v need to match with corresponding any clause. (removed this restriction in 4.6.2)
  4. It is AGGREGATE query with out GROUP BY that means results will be always 1. So you no need ORDER BY, LIMIT, OFFSET.

NOTE: If you are planning to use the query parameters like … IN $avatar , build query text dynamically and do adhoc query as desribed in N1QL query with IN Clause - not working perform better.

FYI: You should also check Spans of IndexScan section of EXPLAIN. The more values of predicates present more better.