Help with array indexing where multiple properties areused

Couchbase Server
1

I am working with 4.6 and I have been through ever section of the Array index documentation and I have figure this out. It seems like the core scenario is that you create in index with that looks simialr to Distinct ARRAY [item.p1, item.p2] for item in items END

but using the predicate of someArray = [“v1”, v2"] is not workable from withing a query since the cross product of all of the items I need to filter on could be VERY large. I’ve tried using several SATISFIES claues but hat hasn’t worked.

Here is the query I am working with:

explain

select v.visitNumber,
v.tenantName,
[oo] as opportunities,
v.admitDate,
v.dischargeDate,
v.primaryInsName,
v.facilityCode,
v.finalCoder,
v.dischargeDepartment,
v.drgCode
from app v
unnest v.opportunities as oo
where v.type = "visit"
and v.tenantName = “t”

and

 any o in v.opportunities SATISFIES (o.state in ["New", "InProcess","PendingReview"]
 OR (o.state = "ChangeApplied" and o.rebillState = 10)) AND
 o.assignedToTeamId in  ["team1", "team2", "team3"]
 END

and oo.assignedToTeamId in [“team1”, “team2”, “team3”]
and (oo.state in [“New”, “InProcess”,“PendingReview”]
OR (oo.state = “ChangeApplied” AND oo.rebillState = 10))

3

If u need multiple properties index one property that has highest selectivity and then other values apply post scan index filter.

Example:

CREATE index ix1 ON app(tenantName, DISTINCT ARRAY o.assignedToTeamId FOR o IN opportunities END) WHERE type = "visit";

1 Like
4

unfortunately no single property is selective enough. I really need the combination of state and rebillState. Is there any form of concatenation that works? Can I use multiple indexes?

5

You can use two different indexes it will do IntersectScan.

If second filter is fixed set you can try with WHEN clause in the index creation

CREATE index ix1 ON app(tenantName, DISTINCT ARRAY [o.state,o.assignedToTeamId] FOR o IN opportunities END) WHERE type = "visit";

SELECT v.visitNumber, v.tenantName, [oo] as opportunities,
       v.admitDate, v.dischargeDate, v.primaryInsName, v.facilityCode,
       v.finalCoder, v.dischargeDepartment, v.drgCode
FROM app v
UNNEST v.opportunities AS oo
WHERE v.type = "visit" AND v.tenantName = "t"
      AND ANY o IN v.opportunities SATISFIES [o.state,o.assignedToTeamId] IN
                  [["New", "team1"], ["InProcess", "team1"], ["PendingReview","team1"], ["ChangeApplied","team1"],
                   ["New", "team2"], ["InProcess", "team2"], ["PendingReview","team2"], ["ChangeApplied","team2"],
                   ["New", "team3"], ["InProcess", "team3"], ["PendingReview","team3"], ["ChangeApplied","team3"]]
           END
       AND oo.assignedToTeamId in ["team1", "team2", "team3"]
       AND (oo.state in ["New", "InProcess","PendingReview"]
           OR (oo.state = "ChangeApplied" AND oo.rebillState = 10))

Try above. ANY clause produces more data as you are not applying rebillState but after unnest those will be eliminated. by oo predicate.

6

Just to follow up after all this time: We ended up using two index and an intersect scan. Works quite well.

7

@DrGarbinsky, Thanks for update.