Bad performance with N1QL

SQL++
1

Hi,
We are integrating Couchbase in our project and we are observing some performance issues when using N1QL querys.

Our cluster configuration parameters are the following:
Data RAM Quota: 4718 MB
Index RAM Quota: 512 MB
Full Text RAM Quota: 512MB
Bucket Per Node RAM Quota: 100MB
Bucket Cache Metadata -> Value Ejection

We are using the sample buckets with their corresponding indexes and one server node.

As an example of our problem the following query:

select * from ‘travel-sample’ where type = ‘route’;

Returns 24024 documents and takes 5.57 seconds to execute.

How can we reduce the execution time?

2

Hello,
One step is see the execution query excution plan.
You can get with explain select * from ‘travel-sample’ where type = ‘route’;
And second, if the execution plan use the primary index, créate an index on type attribute with créate index command.
Are you using only a Couchbase server or cluster od varios nodes?

Regards
Arturo

1 Like
3

Hi Arturo,

This is the execution query plan:

[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “def_type”,
“index_id”: “89a2742f77a4b020”,
“keyspace”: “travel-sample”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“route”"
],
“Inclusion”: 3,
“Low”: [
"“route”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “travel-sample”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “((travel-sample.type) = “route”)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “select * from travel-sample where type = ‘route’;”
}
]

Only have a Couchbase server.

Thanks you.

4

Hello,
How is defined you index def_type?
You can see the index definition from Index option at Couchabase GUI.

Arturo