N1QL performance with join

0
votes

I am suffering with N1QL's performance. I have 3 node 4.1 cluster setup, with 6gb each node and 1 replica set. Total of 2 million documents of average size 100k inserted. While selecting document using N1QL, query has join with in same bucket so might look like its self join. I am getting data in 21 minutes. Which is horrible. On the key I have join, I have already created index. What else am I missing. To me if ForestDB is truly working it should give me the result in sub - second. Looking for an answer here. Didn't get much support from couchbase forums though.

1
performancecouchbasen1qlbigdata
Can you tell us which document has No_ as a primary key, and which one has No_ as a foreign key? I masking about N and X. That is, do the N documents have No_ as primary key, or do the X documents have No_ as a primary key? - geraldss
Could you update your question with the N1QL query? That might shed some light into the problem... - user1697575
As posted, it's not a question and the discussion of this performance optimization is here: forums.couchbase.com/t/perfomance-issue-with-n1ql-self-join/… . I'm not sure why you say "didn't get much support" as my colleagues at Couchbase are trying to assist you there. - Matt Ingenthron
Sure I am moving this there. Basically I am evaluating for production usage and the performance I was getting was in minutes not even in seconds. That is why I posted here as well to get traction and some obvious answers from others. Looks like I am only one trying to use N1QL. - siddhusingh

1 Answers

3
votes

Please create the following index and try the query below it.

    CREATE INDEX idx_gle_type_balance2 ON NAV(No_, Balance, Type) WHERE (Type = 'GLEntry') USING GSI;

select 
X.No_ AS No_,
IFNULL(Sum(X.Balance),0) as Balance
from NAV X USE INDEX (idx_gle_type_balance2)
Where X.Type = "GLEntry" 
and X.Balance IS NOT MISSING
AND X.No_ IS NOT MISSING
Group by X.No_;

----- Update from Siddu is, with the new index in place, query runs in about 1.7 seconds.