Couchbase Query Optimization

Couchbase Server
1

Hi Team,

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

cb_query.zip (393 Bytes)

2

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

3

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

SELECT DISTINCT RAW A.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]))) ;
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.

Thanks,
Debasis

5

If using EE run the query in Query Work Bench and provide output (profile stats ) from Plan Text tab.

6

cb_plan_text.zip (1.7 KB)
@vsr1 Please find the attached o/p from plan text tab for the query initially I had mentioned.

7

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

8

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