Perfomance issue with N1QL self join

SQL++
21

Same result.

Query execution time : 11.83 s
Explain Query :

[{
"#operator": “Sequence”,
"~children": [{
"#operator": “IntersectScan”,
“scans”: [{
"#operator": “IndexScan”,
“index”: “idx_gle_type_balance”,
“keyspace”: “NAV”,
“namespace”: “default”,
“spans”: [{
“Range”: {
“Inclusion”: 1,
“Low”: [
“null”,
“null”
]
}
}],
“using”: “gsi”
}, {
"#operator": “IndexScan”,
“index”: “idx_GLE_Type”,
“keyspace”: “NAV”,
“namespace”: “default”,
“spans”: [{
“Range”: {
“High”: [
"“GLEntry”"
],
“Inclusion”: 3,
“Low”: [
"“GLEntry”"
]
}
}],
“using”: “gsi”
}]
}, {
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [{
"#operator": “Fetch”,
“as”: “X”,
“keyspace”: “NAV”,
“namespace”: “default”
}, {
"#operator": “Filter”,
“condition”: “((((X.Type) = “GLEntry”) and ((X.Balance) is not missing)) and ((X.No_) is not missing))”
}, {
"#operator": “InitialGroup”,
“aggregates”: [
“sum((X.Balance))”
],
“group_keys”: [
"(X.No_)"
]
}]
}
}, {
"#operator": “IntermediateGroup”,
“aggregates”: [
“sum((X.Balance))”
],
“group_keys”: [
"(X.No_)"
]
}, {
"#operator": “FinalGroup”,
“aggregates”: [
“sum((X.Balance))”
],
“group_keys”: [
"(X.No_)"
]
}, {
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [{
"#operator": “InitialProject”,
“result_terms”: [{
“as”: “No_”,
“expr”: “(X.No_)”
}, {
“as”: “Balance”,
“expr”: “ifnull(sum((X.Balance)), 0)”
}]
}, {
"#operator": “FinalProject”
}]
}
}]
}]

22

Any thoughts here guys ? @keshav_m and @geraldss ? Same query with SQL server with ~ 3 million records is under 1 second .

23

Please execute the following query and you should see better performance.

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

24

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.

1 Like
25

Hi @keshav_m, can you please post the same answer on Stack Overflow so others can find it? Here is the question on Stack Overflow:

Thanks,
Gerald

26

Yes it performed really well with proper index creation. I think Explain query should also be explained well in the documentation with a use-case as an example. Thanks for the help.

1 Like
27

For views as well do we get benefit by having dedicated Index and query node ?
Or all the nodes should be having all index + query + data ?

28

Views are currently always co-located with the KV nodes.

29

Even if I dedicate a server explicitly to keep only data.

30

Yes if it keeps only data it will also keep the views if you have defined any.

31

Thanks @vmx. Also suppose I want to use Views but pre-calculated data and N1QL for some adhoc queries. Then should I create my cluster with query and index separately and data node separately. Will it suffice both the need efficiently ?

32

I’ve too little experience with sizing Couchbase, I sadly can’t help here.

33

@geraldss, @keshav_m, any input on my question please.

34

Hi @singh_siddhu,

Are you referring to your question about views? Or a different question?

We can get you on a Skype call with @keshav_m. Let us know.

Thanks,
Gerald

35

In some use cases, @singh_siddhu, wants to join, group & aggregate over > 200K rows and within sub seconds. For that, view based pre-calculation may help.
Views & KV always co-exist.

For production use, we recommend you having index and query services on separate nodes.

36

Got the answer from @keshav_m on skype. Posted it here for public view. Basically we need query / index servers separate from data nodes. But this would help in N1QL only. For views, doesn’t matter looks like because it stays close to data. Then again the question arises : N1QL will also need data from all the nodes. So does it mean that it all happens from index node and therefore, no map-reduce is required ? I think a comparison of N1QL versus View as document is required. What to use when ? From evolution of Couchbase it gives impression that we should get rid of views and start using N1QL.