Hi @vsr1
We are looking for some couchbase lookup queries and while getting them from Db, we are getting high response time , though we have added the necessary indexes.Needed your help to fine grain them.
WITH pages AS (
SELECT page.pageId,
page.allowPastInstance ,
*
FROM b1 AS page
WHERE page.type = ‘page’
AND page.clientId = “clientId”
AND page.pageId IN [“pageId”] ) SELECT MAX([ instance.expiryTime, { instance , ‘page’: p.page , ‘_ID’: META(instance).id, ‘_CAS’:META(instance).cas }])[1].*
FROM b1 AS instance
JOIN pages AS p ON instance.pageId = p.pageId
WHERE instance.isStacked = FALSE
AND instance.type = ‘pagedef’
AND instance.clientId = “clientId”
AND ( (instance.effectiveTime <= 1618913036647
AND 1618913036647 <= instance.expiryTime)
OR (instance.effectiveTime <= 1618913036647
AND instance.expiryTime IS NULL )
OR (p.allowPastInstance = TRUE
AND instance.expiryTime < 1618913036647))
GROUP BY instance.pageId
Index Used : CREATE INDEX index_instance ON b1(clientId,isStacked,pageId,effectiveTime,expiryTime) WHERE (type = ‘pagedef’)
Average Response Time :150ms , we are looking for some thing below 30ms.
Please let us know if there is an alternative approach for this query,