Slow query speed even with intersect scan of two indexes, high query time variability

SQL++
2

As you are looking same user in both the fields Instead of OR use ARRAY index (converting both fields in to ARRAY and look inside the array)

AVOID IntersectScans by providing USE INDEX hint.

Also index WHERE clause values must be single or double quote (not back-ticks. other wise it considers field in the document) ex: afterfull, stateUpdate

Use EXPLAIN and checkout spans section and understand what values it passed to IndexScan. More exact values passed from predicate will perform better.

   CREATE INDEX ix1 ON `vigorlending`(event,  DISTINCT [`globalStats`.`bailoutuser`,`globalStats`.`bailoutupuser`],  block.num DESC)
        WHERE  `type`="stateUpdate";

SELECT *
FROM vigorlending
WHERE  ANY v IN  [`globalStats`.`bailoutuser`,`globalStats`.`bailoutupuser`] SATISFIES v = "targetuser" END
    AND event="afterfull"
    AND type="stateUpdate"
ORDER BY block.num DESC;

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

Response from this post Identify top N queries in couchbase has details how to identify which part of query taking time.

1 Like