Couchbase Query Optimization

Hi Team,

Could someone help me to optimize the attached query. The plan also attached. (1.0 KB) (393 Bytes)

Please find the index as attached and with this index it completes in 6s. Is there any opportunity (302 Bytes)
to further optimize the query.

CREATE INDEX ix2 ON config(__t, numtype, hpmn, __at, status,name) WHERE __t IN ["vf-group", "vf-group-range"] AND enabled = TRUE ;

Use query results as your name

FROM `config` A
WHERE A.status <> 2
      AND A.numtype = 1
      AND A.__at > 0
      AND A.hpmn= 201
      AND enabled= TRUE
      AND ((A.__t='vf-group' AND SPLIT(META(A).id, ':')[3] LIKE '123475%')
           OR (A.__t='vf-group-range' AND 123475 BETWEEN TONUMBER(SPLIT(META(A).id, ':')[3]) AND TONUMBER(SPLIT(META(A).id, ':')[4]))) ;

Thanks @vsr1 for your response. I tried to create index and ran the update query shared by you but still takes time 5.7s. DEV team need to complete in 1s so that it will help them . Please let me know if you need any other details.


If using EE run the query in Query Work Bench and provide output (profile stats ) from Plan Text tab. (1.7 KB)
@vsr1 Please find the attached o/p from plan text tab for the query initially I had mentioned.

You still running original query and index. By looking it qualifies 100K items and i am afraid anything can be done here.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.