Query taking more time suggest index

Couchbase Server
1

SELECT split(td.policy.id,"::")[3] as a
FROM
data po JOIN data p ON p.id= po.policy.id
JOIN data pco ON p.id= pco.policy.id JOIN data td ON p.id= td.policy.id
WHERE p.type_ = “Policy” AND po.type_ = “ProductOption” AND pco.type_ = “ProductComponentOption”
AND td.type_ = “TransactionDetail” and td.type=‘NEW CONTRACT PROPOSAL’ and td.effectiveDate =“1999-02-09”
limit 1;

please suggest index

2

You have selective predicate on td , so move that left most in the join and it will performs better.

SELECT split(td.policy.id,"::")[3] as a
FROM data AS td
JOIN data AS p  ON p.id = td.policy.id
JOIN data AS po ON p.id = po.policy.id
JOIN data AS pco ON p.id = pco.policy.id
WHERE p.type_ = "Policy" AND po.type_ = "ProductOption" AND pco.type_ = "ProductComponentOption"
AND td.type_ = "TransactionDetail" AND td.type="NEW CONTRACT PROPOSAL" AND td.effectiveDate ="1999-02-09"
LIMIT 1;

CREATE INDEX ix1 ON data (type, effectiveDate, policy.id) WHERE type_ = "TransactionDetail";
CREATE INDEX ix2 ON data (policy.id) WHERE type_ = "ProductComponentOption";
CREATE INDEX ix3 ON data (policy.id) WHERE type_ = "ProductOption";
CREATE INDEX ix4 ON data (id) WHERE type_ = "Policy";

You can try Index Advisor https://query-tutorial.couchbase.com/tutorial/#55