Query optimization as its taking more time

SQL++
1

Hi Team,

Can you please provide the suitable alternative query for below n1ql or right index which improves the performance of this query:

SELECT count(*) FROM bucket1 AS bckt WHERE doc_type = ‘ABCD’ AND (FIRST v1.attributeName FOR v1 IN bckt.definingAttributes.attributes WHEN lower(v1.attributeType)= ‘efgh’ END) AND (ANY i IN items SATISFIES i.isSwatchPresent = false END) AND ARRAY_SUM(ARRAY 1 FOR item IN bckt.items WHEN item.genericAttribute IS MISSING END) >=1 And bckt.statusDetail in [“XYZ”]

2

Have you tried https://index-advisor.couchbase.com/indexadvisor/#1 for a suitable index?

3

Yes, its not showing best index for this query.

4

I’d probably try:

SELECT count(*) 
FROM bucket1 AS bckt 
WHERE doc_type = 'ABCD' 
AND (FIRST v1.attributeName FOR v1 IN bckt.definingAttributes.attributes WHEN lower(v1.attributeType)= 'efgh' END) 
AND (ANY i IN items SATISFIES i.isSwatchPresent = false END) 
AND (ANY item IN bckt.items SATISFIES item.genericAttribute IS MISSING END)
AND bckt.statusDetail = "XYZ"

for which the index advisor suggests:

CREATE INDEX adv_doc_type_DISTINCT_items_isSwatchPresent_statusDetail ON `bucket1`(`doc_type`,DISTINCT ARRAY `i`.`isSwatchPresent` FOR i in `items` END,`statusDetail`);
5

What about attributeName and attributeType. I don’t see that field in index

6

Assumed isSwatchPresent, genericAttribute conditions must be matched same array position (i.e same object from the array)

CREATE INDEX ix1 ON bucket1(statusDetail, DISTINCT ARRAY lower(v1.attributeType) FOR v1 IN definingAttributes.attributes END)
WHERE doc_type = "ABCD" AND ANY item IN items SATISFIES item.isSwatchPresent = false AND item.genericAttribute IS MISSING END;

SELECT count(1)
FROM bucket1 AS bckt
WHERE bckt.doc_type = "ABCD"
     AND ANY item IN items SATISFIES item.isSwatchPresent = false AND item.genericAttribute IS MISSING END
     AND ANY v1 IN bckt.definingAttributes.attributes SATISFIES lower(v1.attributeType) = "efgh" AND v1.attributeName IS NOT NULL END
     AND bckt.statusDetail in ["XYZ"]

Make sure ANY clause from index WHERE present exactly same (including variable v1) in the query. If some reason it can’t be present remove from the index

In 7.1, try following and see if it covers

CREATE INDEX ix1 ON bucket1(statusDetail, DISTINCT ARRAY FLATTEN_KEYS(lower(v1.attributeType), v1.attributeName) FOR v1 IN definingAttributes.attributes END)
WHERE doc_type = "ABCD" AND ANY item IN items SATISFIES item.isSwatchPresent = false AND item.genericAttribute IS MISSING END;