Improve Couchbase Query for large dataset

Question

I have below bucket in Couchbase I am using inner join to fetch the document

We have global secondary index for parent and child document.

• Total records in bucket : 4,300,000
• Query Execution : 5-7 sec
• RAM Used is 32 GB (allocated 90 GB)
• Disk Used is 20 GB

Its like we have parent document and making the several copy of child document with parent document

Example:

{
"document_id" : "p-001",
"document_desc" : "parent doc 1",
"document_type" : "parent"
},
{
"document_id" : "c-001",
"parent_document_id" : "p-001",
"document_desc" : "child doc of parent 1",
"document_type" : "child",
"document_status" : "NEW",
"customer_id" : "c-001"
},
{
"document_id" : "c-002",
"parent_document_id" : "p-001",
"document_desc" : "child doc of parent 1",
"document_type" : "child",
"document_status" : "NEW",
"customer_id" : "c-001"
}

I am using inner join to fetch the document

SELECT * FROM 
test_document AS parent 
JOIN 
test_document AS child
ON child.parent_document_id = parent.document_id
WHERE child.customer_id = 'c-001'
AND child.document_status IN ('NEW', 'OLD', 'OLDER')

Can I improve this query to make the query execution within few milliseconds (before 1 second)? Or any other thoughts to achieve query execution before 1 seconds?

Answer 1

CREATE ix1 ON test_document (customer_id, document_status, parent_document_id);
CREATE ix2 ON test_document (document_id);

As you have predicate on child , switch JOIN order

SELECT *
FROM test_document AS child
JOIN test_document AS parent ON child.parent_document_id = parent.document_id
WHERE child.customer_id = 'c-001' AND child.document_status IN ['NEW', 'OLD', 'OLDER'];