Hello,

Need some help to understand why is the index NOT a covered index for the query.

Couchbase server version is 6.0 & 6.6. codes is an array.

Index: CREATE INDEX `idxccndc`

ON `config`

((distinct (array ((`v`

.`cc`

) || (`v`

.`ndc`

)) for `v`

in `codes`

when ((`v`

.`primary`

) = 1) end))) WHERE (`__t`

= “irdb-net”);

Query:

SELECT count(*)

FROM config x

WHERE

ANY v in codes satisfies position(“30697111”,v.`cc`

||v.`ndc`

) = 0

AND v.`primary`

= 1 END

AND x.`__t`

= “irdb-net”;

We tried various combinations mentioned below but could not do away with the FETCH.

(1) created 3 array indices one with each element as the key

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”);

(2) created a single array index with all 3 elements

CREATE INDEX `idxpriccndc`

ON `config`

(distinct array [v.`primary`

,v.`cc`

,v.`ndc`

] for `v`

in `codes`

when (`v`

.`primary`

) = 1 end) WHERE (`__t`

= “irdb-net”);

(3) created a single array index with all 3 elements

CREATE INDEX

`idxpriccndc`

ON

`config`

(distinct array [(v.`primary`

),(v.`cc`

),(v.`ndc`

)] for `v`

in `codes`

when (`v`

.`primary`

) = 1 end)

WHERE (`__t`

= “irdb-net”);

Thanks