Index scan with OR operator

SQL++
1

We create three indexes:

CREATE INDEX type_index ON bucket1(type) using gsi;
CREATE INDEX id1_index ON bucket1(id1) WHERE type=“t1” using gsi;
CREATE INDEX id2_index ON bucket1(id2) WHERE type=“t1” using gsi;

If I write the query like this

select doc
from bucket1 doc
where doc.type=“t1”
and (doc.id1 in [“123”] AND doc.id2 in [“123”])

Then it will hit an IntersectScan across all indexes which is expected

However if I change the operator that connects id1 and id2 to OR operator, then Couchbase only use type_index but do not perform union scan on id1_index and id2_index

select doc
from bucket1 doc
where doc.type=“t1”
and (doc.id1 in [“123”] OR doc.id2 in [“123”])

Any suggestion how to optimize the second query?

2

Hi, you can use UNION or UNION ALL, depending on the possibility of duplicates:

select doc
from bucket1 doc
where doc.type="t1"
and doc.id1 in ["123"]
union
select doc
from bucket1 doc
where doc.type="t1"
and doc.id2 in ["123"]
;