Very slow performance on ANSI JOIN from different bucket

SQL++
1

Hello,
I have 2 bucket - AccountFirstId(38,323,647 documents ) / SubscriberInfo(43,874,195 documents ).
It looks like this:
CREATE INDEX IDVIEW_index1 ON AccountFirstId(FIRSTID, CONTR_COMPONENT_ID) USING GSI;
{
“AccountFirstId”: {
“CONTRACT_ROLE_ID”: “102243729531306002”,
“CONTR_COMPONENT_ID”: “101043729531303902”,
“FIRSTID”: “A123456789”,
}
}

CREATE INDEX IDVIEW_index2 ON SubscriberInfo(ACT_CONTR_COMPONENT_ID, CONTR_COMPONENT_ID) USING GSI;
{
“SubscriberInfo”: {
“ACCOUNT_ID”: “413549014”,
“ACT_CONTR_COMPONENT_ID”: “108243565596500004”,
“ACT_PARTY_ID”: “103943560327366403”,
“ADMIN_FLD_NM_TP_CD”: “120”,
“CONTRACT_ID”: “104743565596499504”,
“CONTR_COMPONENT_ID”: “101043565596640101”,
}
}

I executed what I thought was a simple N1QL Query in Couchbase version 5.5.2
select INFO.*
from AccountFirstId AFIRSTID
JOIN SubscriberInfo INFO ON (INFO.ACT_CONTR_COMPONENT_ID = AFIRSTID.CONTR_COMPONENT_ID)
where AFIRSTID.FIRSTID =“A123456789”

I have created index but the result take about 5 minutes.(Total data is about 320,151 documents.)

i really don’t understand why got this problem because this really a simple usecase?
does someone can help?

2

You are Joining Millions of documents and producing more than quarter million documents. It takes time. you can enable Profiling and checkout where it taking time.
If you use EE you can try with HASH JOIN https://blog.couchbase.com/ansi-join-support-n1ql/

3

Thanks for the comments @vsr1,
Here are the query results along with performance numbers:

issue1
issue1811×288 17.7 KB

Please help me understand this performance and if you have any comments regarding the benefits-costs of replacing SQL with a document database(Couchbase) pertinent to the example(above) please don’t hesitate.

4

You are doing 300K items Joined 300K items in nested loop. It takes time. You can try with HASH JOIN in EE version.

Please go through the following articles and see where time spent.

https://blog.couchbase.com/optimize-n1ql-performance-using-request-profiling/

https://blog.couchbase.com/a-guide-to-n1ql-features-in-couchbase-5-5-special-edition/ https://blog.couchbase.com/category/n1ql-query/