Need Help With Couchbase Indexes

SQL++
,
1

Hi @vsr1 ,

I need help with index for the below query

SELECT MAX([ instance.expiryTime, { page, instance , ‘_ID’: META(instance).id, ‘_CAS’:META(instance).cas }])[1].*
FROM a-bucket AS instance
JOIN a-bucket AS page ON (instance.pageId = page.pageId)
WHERE page.type = ‘page’
AND page.clientId = “demo”
AND instance.isStacked = FALSE
AND instance.pageId IN [“facebook”]
AND instance.clientId = demo"
AND instance.type = ‘pagedef’
AND ((instance.effectiveTime <= 1616514296114
AND 1616514296114 <= instance.expiryTime)
OR (instance.effectiveTime <= 1616514296114
AND instance.expiryTime IS NULL)
OR (page.allowPastInstance = true
AND instance.effectiveTime <= 1616514296114))
GROUP BY instance.pageId

Current Indexes used are

CREATE INDEX index_instance ON a-bucket(clientId,isStacked,pageId,effectiveTime,expiryTime) WHERE (type = ‘pagedef’

CREATE INDEX fragment_cache ON a-bucket(type,clientId)

CREATE INDEX index_page ON a-bucket(clientId,externalId,pageId) WHERE (type = ‘page’)

This is taking a lot of time around 2 seconds to get the result.

If I remove the below OR condition from the above query
OR (page.allowPastInstance = true
AND instance.effectiveTime <= 1616514296114)

the time comes drastically down to 65ms.

No of approx. documents in the Db : 20000

Can you suggest how can I modify my indexes , I need to keep that OR condition.

2 
SELECT MAX([instance.expiryTime, { page, instance , "_ID": META(instance).id, "_CAS":META(instance).cas }])[1].*
FROM `a-bucket` AS instance
JOIN `a-bucket` AS page ON instance.pageId = page.pageId
WHERE page.type = "page"
      AND page.clientId = "demo"
      AND instance.isStacked = FALSE
      AND instance.pageId IN ["facebook"]
      AND instance.clientId = "demo"
      AND instance.type = "pagedef"
      AND instance.effectiveTime <= 1616514296114
      AND (instance.expiryTime >= 1616514296114
           OR instance.expiryTime IS NULL
           OR page.allowPastInstance = true)
GROUP BY instance.pageId;


CREATE INDEX index_instance ON `a-bucket`(clientId,isStacked,pageId,effectiveTime,expiryTime, META().cas) WHERE type = "pagedef"
CREATE INDEX index_page ON `a-bucket`(clientId, pageId, externalId, allowPastInstance ) WHERE type = "page";
3

Thanks, @vsr1,
These indexes helped to decrease the response time.
But there is a catch when we are adding this condition page.allowPastInstance = true
response time is jumping from 20 ms to 400 ms. This allowPastInstance key is optional it is set as true when present otherwise it is not present in the document.

Is there any thing we can do in such scenario ?

Thanks

4

try this

CREATE INDEX index_page ON a-bucket(clientId, pageId, allowPastInstance, externalId ) WHERE type = "page";