Thanks @dh
We have tried LIKE but that too does not work. And I guess that is because, in our case, the string is always longer than the concatenation, so we cannot append the % to the string; instead we have to append % to the field concatenation and hence the fields are on the RHS. e.g.
“30697111” LIKE v.cc||v.ndc||‘%’
Basically, we know that there will only be 1 document if at all. So, we are actually ok with the FETCH as far as indexscan returns only that 1 record. But somehow that is not happening for e.g. we tried below query & index, and ItemsOut is same as all items in the index as a result of which the number of documents being fetched from Data service are high and hence the filter takes long:
ANY v IN codes SATISFIES (v.primary) = 1
AND v.cc = SUBSTR(‘3069711’,0,length(v.cc))
AND v.ndc = SUBSTR(‘3069711’,length(v.cc),length(v.ndc))
END
CREATE INDEX idxpri ON config((distinct (array (v.primary) for v in codes when ((v.primary) = 1) end))) WHERE (__t = “irdb-net”)
CREATE INDEX idxcc ON config((distinct (array (v.cc) for v in codes when ((v.primary) = 1) end))) WHERE (__t = “irdb-net”)
CREATE INDEX idxndc ON config((distinct (array (v.ndc) for v in codes when ((v.primary) = 1) end))) WHERE (__t = “irdb-net”)
When there are only 5 cc values that satisfy the condition, why does indexscan still return all 1055 IDs from the index? Similarly, when there are only 7 ndc values that satisfy the condition, why does indexscan still return all IDs from the index?