Couchbase Query Optimization in CB

Couchbase Server
1

hi @vsr1 . We had one below query where user is fetching 7million records and took almost 57s. Is there any way we can further optimize the query. Plan is attached to query

SELECT COUNT(*)
FROM config A
WHERE A.status <> 2
AND A.numtype = 1
AND A.__at > 0
AND A.hpmn= 6029
AND enabled= TRUE
AND TONUMBER(SPLIT(META(A).id, ‘:’)[2])= 3
AND (A.__t=‘vf-group’
OR A.__t=‘vf-group-range’);
Below is the covering index query is using.
CREATE INDEX vf_group_covering_idx ON config(__t,__at,status,hpmn,numtype,name,enabled) WHERE ((self.__t) in [‘vf-group’, ‘vf-group-range’])
cb_plan_text.zip (1.6 KB)

2

As it is, it isn’t covering - the TONUMBER() filter has to be applied after the index scan and the grouping takes place in Query.

If you define the index as:

CREATE INDEX vf_group_covering_idx ON config(
    __t,__at,status,hpmn,numtype,name,enabled,TONUMBER(SPLIT(META().id, ':')[2])
)
WHERE ((self.__t) in ['vf-group', 'vf-group-range'])

it should be fully covered and can then take advantage of index grouping. (You should see an index_group_aggs element then in the index scan operator in the plan.)

HTH.

1 Like
3 
CREATE INDEX ix2 ON config(__t, numtype, hpmn, __at, status) WHERE __t IN ["vf-group", "vf-group-range"] AND enabled = TRUE AND TONUMBER(SPLIT(META().id, ":")[2])= 3;

SELECT COUNT(1)
FROM config A
WHERE status <> 2
      AND numtype = 1
      AND __at > 0
      AND hpmn = 6029
      AND enabled = TRUE
      AND TONUMBER(SPLIT(META().id, ":")[2])= 3
      AND __t IN ["vf-group", "vf-group-range"];
4

@Debasis_Mallick - out of curiosity, did you use the Index Advisor on that query to get a suggested index definition?

5

Yes Perry I checked but it shows exist index will suffice like that on message shows.

6

Thanks @Debasis_Mallick . Can you confirm which version of Couchbase you’re using? @vsr1 is this something the index advisor can be improved for?

7

@perry . We are using the CB version 6.6.5 EE.

8

Thank you @Debasis_Mallick . Just to make sure you’re aware, Couchbase Server 6.6 reached end of maintenance in Jan of this year and will reach full end of life in Oct 2023. I would strongly suggest upgrading to 7.2 as soon as you are able to. Please consider opening a support ticket with our team if you need any guidance or assistance for your specific use case and requirements.

9

Thanks @perry . We had informed the site team for same but we had some application dependency to upgrade, which is in progress.

10

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