Dear team, We are using couchbase community version 6.6
Few weeks back we had upgraded the couchbase version from 5.1 to 6.6 and after that upgrade the queries which contain the builtin function like UPPER, CONTAINS, SPLIT are providing response in apprx. 3 sec but same query when written without builtin function it provides response in 200 millisec. Can you suggest if community version 6.6 does not support the builtin fucntions?
Have you checked your query plans to see that they’re still using the expected indexes etc. ? It seems most likely that with the addition of one of the functions in the WHERE clause, an index is no longer being used / the plan has changed.
(If the functions weren’t supported, you’d get errors - they are, so you don’t.)
If need be, please post an example query and execution plans with and without the built-in function.
With Inbuilt Function :
SELECT META(business).id AS __id,
META(business).cas AS __cas,
conflictWO,
cowStatus,
icStatus,
icEnvironmentalIsolation,
woNumber
FROM business
WHERE UPPER(docType) = UPPER(‘activity’)
AND CASE WHEN ‘ALL’ = ‘ALL’ THEN TRUE ELSE organisation = ‘ALL’ END
AND cowStatus IN [“PENDING”, “PLANNED”, “RELEASED”, “PAUSED”,“ACTIVE”]
Without Inbuilt Function :
SELECT META(business).id AS __id,
META(business).cas AS __cas,
conflictWO,
cowStatus,
icStatus,
icEnvironmentalIsolation,
woNumber
FROM business
WHERE docType = 'activity’
AND CASE WHEN ‘ALL’ = ‘ALL’ THEN TRUE ELSE organisation = ‘ALL’ END
AND cowStatus IN [“PENDING”, “PLANNED”, “RELEASED”, “PAUSED”,“ACTIVE”]
That almost certainly changes index selection. Please explain both variants and check how the plans differ.
(Ref: EXPLAIN | Couchbase Docs )
Further, an index such as:
CREATE INDEX ix1 ON `business`(UPPER(`docType`),`cowStatus`)
would likely be needed to improve the performance of the variant using UPPER(). (If such an index exists, then hopefully the plan will show its selection/use.)
1 Like
Thank you this helped us to improve performance.