Unnest query running slow

Couchbase Server
,
1

Hi Team,
We are facing issue with one of the query. Its taking more time for its run. Can anyone please provide the alternate query and right index for it.
Below is the query and index currently used:

Query:
SELECT tab.itemNumber, tab.modelId, tab.country, tab.vendorNumber, tab.createdTimeStamp, tab.updateDateTime, tab.docType, tab.createdBy, tab.updateBy, tab.restrictionVersion , ARRAY_AGG(rest) as restrictions FROM bucketA as tab UNNEST tab.restrictions as rest WHERE tab.createdTimeStamp is not null and rest.beginDate BETWEEN xxxxx AND xxxxx AND tab.docType = ‘xxxx’ GROUP BY tab.itemNumber, tab.modelId, tab.country, tab.vendorNumber, tab.createdTimeStamp, tab.updateDateTime, tab.docType, tab.createdBy, tab.updateBy, tab.restrictionVersion OFFSET 0 LIMIT 2

Index:
CREATE INDEX test ON bucketA(createdTimeStamp desc,ALL ARRAY rest.beginDate FOR rest IN restrictions END,itemNumber,modelId,country,vendorNumber,updateDateTime,createdBy,updateBy,restrictionVersion) WHERE docType = ‘xxxxx’ and createdTimeStamp is not null

2

It is GROUP by query and must produce all possible (blocking operation) values even through LIMIT 2. So it takes time.

CREATE INDEX test ON bucketA(ALL ARRAY rest.beginDate FOR rest IN restrictions END,createdTimeStamp desc,
                             itemNumber,modelId,country,vendorNumber,updateDateTime,createdBy,updateBy,
                             restrictionVersion) WHERE docType = "xxxxx";

SELECT tab.itemNumber, tab.modelId, tab.country, tab.vendorNumber,
       tab.createdTimeStamp, tab.updateDateTime, tab.docType,
       tab.createdBy, tab.updateBy, tab.restrictionVersion ,
       ARRAY_AGG(rest) AS restrictions
FROM bucketA AS tab
UNNEST tab.restrictions AS rest
WHERE tab.createdTimeStamp IS NOT NULL
      AND rest.beginDate BETWEEN xxxxx AND xxxxx
      AND tab.docType = "xxxx"
GROUP BY tab.itemNumber, tab.modelId, tab.country, tab.vendorNumber, tab.createdTimeStamp,
         tab.updateDateTime, tab.docType, tab.createdBy, tab.updateBy, tab.restrictionVersion
OFFSET 0
LIMIT 2;

If above query is not covered then use following index.

CREATE INDEX test ON bucketA(ALL ARRAY rest.beginDate FOR rest IN restrictions END,createdTimeStamp desc)
                             WHERE docType = "xxxxx";

You have long GROUP BY, It you are looking per document (not across documents) restrictions for given date range only use the following

SELECT tab.itemNumber, tab.modelId, tab.country, tab.vendorNumber,
       tab.createdTimeStamp, tab.updateDateTime, tab.docType,
       tab.createdBy, tab.updateBy, tab.restrictionVersion ,
       ARRAY rest FOR rest IN tab.restrictions WHEN rest.beginDate BETWEEN xxxxx AND xxxxx  END AS restrictions
FROM bucketA AS tab
WHERE tab.createdTimeStamp IS NOT NULL
      AND ANY rest IN tab.restrictions SATISFIES rest.beginDate BETWEEN xxxxx AND xxxxx  END
      AND tab.docType = "xxxx"
OFFSET 0
LIMIT 2;
3

Thanks @vsr1 . This helps. Will check and get back for any concerns.