N1QL 200x slower than views for simple queries

Couchbase Server
1

So I was deciding if switch from views to N1QL for a game I am developing and so I decided to run some simple benchmark.
Turn out even for a simple select returning all docs of one type there is a 30x difference in performance.
I have setup a test scenario with 200K docs. I then fire 100 concurrent queries and these are the results:

The view returns the data in 300ms average.
N1QL return the data in min 9 seconds max 72 seconds and then even run out of ram.

Running 1000 concurrent.
The view returns in 300ms-3seconds.
N1QL return the data in min 15 seconds max 90 seconds and then some queries run even out of ram.

What’s funny is that when I try to make 1000 concurrent queries with just 5 docs, with n1ql it takes from 4 to 10 seconds on the first run and 500ms to 5 seconds on subsequent runs, with views 400ms to 3 seconds from the first run.

I am using Couchbase 4.5.1 on a mac running inside vmware so it’s quite starved both for cpu and ram but the same apply for views so I shouldn’t be biased.

Here is the plan for the query, did I mistake something:
explain select val from Fitness where type=‘fitsession’

[
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan”,
“index”: “valindex”,
“index_id”: “d587c7d7513389c3”,
“keyspace”: “Fitness”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“"fitsession"”
],
“Inclusion”: 3,
“Low”: [
“"fitsession"”
]
}
}
],
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“keyspace”: “Fitness”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((Fitness.type) = "fitsession")”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(Fitness.val)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “select val from Fitness where type=‘fitsession’”
}
]

2

Here is the N1QL index you should be using.

CREATE INDEX idx_val ON Fitness( val ) WHERE type = 'fitsession';

You should then use a USE INDEX in your query to make sure you are using this index alone.

You can post the EXPLAIN here to confirm.

3

Hi Gerald,
There is already such index on the bucket. I tried adding the use index statement but the performance is the same.
The EXPLAIN seem also identical to before:
[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “valindex”,
“index_id”: “d587c7d7513389c3”,
“keyspace”: “Fitness”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“fitsession”"
],
“Inclusion”: 3,
“Low”: [
"“fitsession”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Fetch”,
“keyspace”: “Fitness”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “((Fitness.type) = “fitsession”)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “(Fitness.val)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “select val from Fitness use index(valindex) where type=‘fitsession’”
}
]

4

Ok. You need to drop all the existing indexes on the bucket.

After that, copy and paste my exact CREATE INDEX statement.

5

Now it complain I have no primary index(cause I deleted that too). Should I create it or something is wrong?
If I recreate it I get the same result as before…

6

Ok, that’s good progress.

Do NOT create the primary index.

Copy and paste here your exact CREATE INDEX and SELECT statements.

7

select val from Fitness use index(idx_val) where type=‘fitsession’

and

CREATE INDEX idx_val ON Fitness(val) WHERE (type = “fitsession”)

8

Are you using cbq shell?

9

No, the benchmark is run with node js with the latest sdk.
The “no primary index” error however appear also when running the query from the web admin interface.
I also tried firing a query from the web interface while the node js benchmark was running and it was still slow, so I doubt it’s a problem of the node sdk.

10

Ok, let’s get it working in cbq shell first.

11

What should I do? Redo all by cqb?

12

Yes, please do that. All using cbq shell.

13

Same as before, no primary index.

14

One thing to keep in mind when testing is that with cbq you are testing a parallelism of 1 instead of 100 so once the one query works as expected with @geraldss help you need to redo with a higher concurrency in the language of choice to make sure the query engine can keep up there as well.

15

Same error as before from cbq shell? Can you run this:

SELECT VERSION();

17

select version();
{
“requestID”: “5f69e3f3-1d5c-4e80-8e4a-ccff63028c59”,
“signature”: {
"$1": “string”
},
“results”: [
{
"$1": “1.6.0”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “1.268749ms”,
“executionTime”: “1.147019ms”,
“resultCount”: 1,
“resultSize”: 37
}
}

I forgot to say there are other docs type other than fitsession
present, Is a secondary index required for each even if the query
doesn’t use them?

18

Yes, I will rerun everything. It seem a bug now however.

19

I just tried removing all non fitsession docs, same error : no primary index.

20

The other docs do not affect the query.

Hi @vsr1, @keshav_m, why is this query not able to use this index?

21

Query need to have predicate need on leading key.
CREATE INDEX idx_val ON Fitness( val ) WHERE type = ‘fitsession’;
select val from Fitness where type=‘fitsession’ AND val is NOT MISSING.