2

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?

Matthew Groves
  • 25,181
  • 9
  • 71
  • 121
  • Would you please include the indexes that you are currently using? And maybe post the query execution plan? – Matthew Groves Apr 28 '23 at 14:45
  • We have created exactly same index as mentioned in below answer Find the index created Index 01 : document_Id Index 02 : document_Id, customer_id, document_status – Jeya Prakash May 02 '23 at 05:49

1 Answers1

2
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'];
vsr
  • 7,149
  • 1
  • 11
  • 10
  • 1
    We have tried above approach, but still no improvement in execution time Check out the number of document below :: Count of parent document : 427362 Count of child document : 3157 – Jeya Prakash May 02 '23 at 05:39
  • 1
    Did you feel number of documents here (to join) might affect the performance ? – Jeya Prakash May 02 '23 at 05:53
  • EE , FROM test_document AS child JOIN test_document AS parent USE HASH (PROBE) – vsr May 03 '23 at 05:41