Issue with Index Usage and Query Performance After Couchbase Version Upgrade

Couchbase Server
,
1

Hi!
We’ve recently upgraded our Couchbase instance to a newer version(5.1 → 6.6) and have encountered some performance issues with query execution. Specifically, we are seeing unexpected behavior in how indexes are being utilized.

We had following indexes on older version, and query was pretty fast:
CREATE INDEX idx_user_email ON member(email) WHERE _type = “User”;
CREATE INDEX idx_user_oidc0_sub ON member(oidc[0].sub) WHERE _type = “User”;
CREATE INDEX idx_user_type ON member(_type);

However, after upgrading version the query planner seems to avoid using them, and the only index being utilized is idx_user_type, which leads to suboptimal query execution plans. We are trying to ensure that queries filter on email, oidc[0].sub, and _type efficiently, especially with conditions like email = 'xyz' OR oidc[0].sub = 'abc'.
We even tried creating a composite index:
CREATE INDEX idx_user_email_oidc0_sub
ON member (
_type,
email,
oidc[0].sub
)
WHERE _type = “User”;

but are still getting very bad performance.

This is the query example:
SELECT *,TOSTRING(META().id) AS id from member
WHERE _type=‘User’
AND (( email = ‘example@example.com’)
OR ( oidc[0].sub = ‘1234567-abc7-1234-1a1a-1234aa123456’))

Do you have any recommendations on how to fix this?

2

This query will return two types of documents. Those with ( _type = ‘User’ and email = ‘example@example.com’, and those with (_type = 'User ’ and oidc [0].sub = ‘1234567-abc7-1234-1a1a-1234aa123456’). So make two indexes, one with _type and email, the other with _type and oidc[0].sub. ( A predicate with ‘OR’ is not useful for an index lookup ) . I don’t recall if 6.6 has Index Advisor in UI Query tab, but that can be useful to show what indexes would be useful.

SELECT *,TOSTRING(META().id) AS id from member
WHERE _type=‘User’
AND (( email = ‘example@example.com’)
OR ( oidc [0].sub = ‘1234567-abc7-1234-1a1a-1234aa123456’))

3

Above query should have used idx_user_email, idx_user_oidc0_sub and done UNION Scan

If not use index hints

SELECT m.*, META().id AS id
FROM member m USE INDEX (idx_user_email, idx_user_oidc0_sub)
WHERE _type = "User"
      AND (( email = "example@example.com")
             OR ( oidc[0].sub = "1234567-abc7-1234-1a1a-1234aa123456"));
1 Like
4

Creating these indexes: CREATE INDEX idx_user_email_type ON member(email,_type) WHERE (_type = “User”)
CREATE INDEX idx_user_oidc_sub_type ON member(((oidc[0]).sub),_type) WHERE (_type = “User”)
made it significantly faster, it is returning results in under 5ms now.

5

Thanks @vsr1 and @mreiche