N1QL query having subquery not picking right index for subquery

SQL++
, ,
1

I have the below query like below

SELECT COUNT(*) AS count FROM test pci 
WHERE pci._class in ['XXXXX','YYYYY'] 
AND pci.effectiveDateTime BETWEEN 1577836800000 AND 2556057600000 
AND TO_NUMBER(pci.meta.changePercentage) >= -12.0 AND 
ARRAY_LENGTH(ARRAY_INTERSECT(pci.locationDSL.parameters.locationClusterId, ["05e7673f-b28d-491a-82b4-08a836112e2a"])) > 0 
AND pci.intentRequest.status IN ["SUCCESS"] AND META(pci).id NOT IN( 
SELECT RAW META(p1).id from test p1 WHERE p1.state = 'DRAFT' AND p1.effectiveDateTime BETWEEN 1577836800000 AND 2556057600000
AND ANY tpnb IN p1.productDSL.parameters.tpnb SATISFIES tpnb IS NOT NULL END
AND p1._class in ['XXXXX','YYYYY'] 
AND p1.intentRequest.request in ['AAAAA','BBBBB'] 
AND p1.intentRequest.status != 'SUCCESS' AND p1.meta.isIntentCreatedByAmend = true)

When executing the subquery separetely it is picking up the right index below

CREATE INDEX idx_1 ON test (effectiveDateTime, ALL productDSL.parameters.tpnb, state, intentRequest.status, intentRequest.request)
PARTITION BY HASH(META().id) WHERE (_class IN [“XXXXX”,“YYYYY”])
USING GSI

But when executing it in the above first query it is picking up

CREATE INDEX idx_2 ON test (effectiveDateTime)
PARTITION BY HASH(META().id) WHERE (_class IN [“XXXXX”,“YYYYY”])
USING GSI

Any help on tuning this query would be of great help?

2

It will pick the idx_1. If not specify USE INDEX.
Subquery is IN clause no way tell which index it picked.

ANY tpnb IN p1.productDSL.parameters.tpnb SATISFIES tpnb IS NOT NULL END

Do u have ARRAY with NULL values? If not this condition is not needed (ARRAY index is not needed).

SELECT RAW META(p1).id from test p1 WHERE p1.state = ‘DRAFT’ AND p1.effectiveDateTime BETWEEN 1577836800000 AND 2556057600000
AND ANY tpnb IN p1.productDSL.parameters.tpnb SATISFIES tpnb IS NOT NULL END
AND p1._class in [‘XXXXX’,‘YYYYY’]
AND p1.intentRequest.request in [‘AAAAA’,‘BBBBB’]
AND p1.intentRequest.status != ‘SUCCESS’ AND p1.meta.isIntentCreatedByAmend = true

Right Index
CREATE INDEX ix1 ON test (state, intentRequest.request , meta.isIntentCreatedByAmend, effectiveDateTime, intentRequest.status, DISTINCT productDSL.parameters.tpnb )
PARTITION BY HASH(META().id) WHERE ( _class IN [“XXXXX”,“YYYYY”])
USING GSI

https://index-advisor.couchbase.com/

Try following. If WITH is not supported in your release replace in actual query

WITH exclude AS (SELECT RAW META(p1).id
                 FROM test p1
                 WHERE p1.state = 'DRAFT'
                       AND p1.effectiveDateTime BETWEEN 1577836800000 AND 2556057600000
                       AND ANY tpnb IN p1.productDSL.parameters.tpnb SATISFIES tpnb IS NOT NULL END
                       AND p1._class in ['XXXXX','YYYYY']
                       AND p1.intentRequest.request in ['AAAAA','BBBBB']
                       AND p1.intentRequest.status != 'SUCCESS'
                       AND p1.meta.isIntentCreatedByAmend = true)
SELECT COUNT(1) AS count
FROM test pci
WHERE pci._class in ['XXXXX','YYYYY']
     AND pci.effectiveDateTime BETWEEN 1577836800000 AND 2556057600000
     AND TO_NUMBER(pci.meta.changePercentage) >= -12.0 AND
     AND ANY v IN pci.locationDSL.parameters.locationClusterId SATISFIES v IN ["05e7673f-b28d-491a-82b4-08a836112e2a"] END
     AND pci.intentRequest.status IN ["SUCCESS"]
     AND META(pci).id NOT IN exclude;

CREATE INDEX ix1 ON test (state, intentRequest.request, meta.isIntentCreatedByAmend, effectiveDateTime,
                          intentRequest.status, DISTINCT productDSL.parameters.tpnb) WHERE _class IN ['XXXXX','YYYYY'] ;

CREATE INDEX ix2 ON test (intentRequest.status, DISTINCT locationDSL.parameters.locationClusterId,
                          effectiveDateTime, TO_NUMBER(meta.changePercentage))
                          WHERE _class IN ['XXXXX','YYYYY'] ;