N1QL vs View Performance Issue

SQL++
1

I tested the following with 4.0 and 4.1.

N1QL Performance on Order By

We are testing N1QL and found a Performance issue when using a order by in the query.
I have index on type and lastName.
Test Case:* ordering by an attribute (lastName)* paging using skip and limiting to 50
The N1QL query returns the same results over 1.6 - 2.5 seconds Select r.id, r.fullName FROM my_bucket r WHERE r.type=‘user’ Order By r.lastName limit 50
Using a view returns back ~700 ms.
If I remove the order by in the N1QL statement, the results come back under 100ms
Thanks.

2

Can you check if both the query with the ORDER BY and the one without use the index you defined (maybe you can post your indexes + name as well as the EXPLAIN SELECT … query).

When you say using a view do you mean you’ve created an index with USING VIEWS instead of GSI or going through a manual map/reduce using our view API?

3

Comparing to map/reduce views.

Indexes:
CREATE INDEX type_idx ON dev_sync_gateway_sw1(type) USING GSI
CREATE INDEX lastName_idx ON dev_sync_gateway_sw1(lastName) USING GSI

“EXPLAIN Select r.id, r.fullName FROM dev_sync_gateway_sw1 r WHERE r.type=‘user’ Order By r.lastName limit 50”

Explain with Order By
[ { "#operator": "Sequence", "~children": [ { "#operator": "Sequence", "~children": [ { "#operator": "IndexScan", "index": "type_locations_idx", "keyspace": "dev_sync_gateway_sw1", "limit": 9223372036854776000, "namespace": "default", "spans": [ { "Range": { "High": [ "successor(\"user\")" ], "Inclusion": 1, "Low": [ "\"user\"" ] }, "Seek": null } ], "using": "gsi" }, { "#operator": "Parallel", "~child": { "#operator": "Sequence", "~children": [ { "#operator": "Fetch", "as": "r", "keyspace": "dev_sync_gateway_sw1", "namespace": "default" }, { "#operator": "Filter", "condition": "((r.type) = \"user\")" }, { "#operator": "InitialProject", "result_terms": [ { "expr": "(r.id)" }, { "expr": "(r.fullName)" } ] } ] } } ] }, { "#operator": "Order", "sort_terms": [ { "expr": "(r.lastName)" } ] }, { "#operator": "Limit", "expr": "50" }, { "#operator": "FinalProject" } ] } ]

EXPLAIN Select r.id, r.fullName FROM dev_sync_gateway_sw1 r WHERE r.type=‘user’ limit 50

[ { "#operator": "Sequence", "~children": [ { "#operator": "Sequence", "~children": [ { "#operator": "IndexScan", "index": "type_idx", "keyspace": "dev_sync_gateway_sw1", "limit": 9223372036854776000, "namespace": "default", "spans": [ { "Range": { "High": [ "\"user\"" ], "Inclusion": 3, "Low": [ "\"user\"" ] }, "Seek": null } ], "using": "gsi" }, { "#operator": "Parallel", "~child": { "#operator": "Sequence", "~children": [ { "#operator": "Fetch", "as": "r", "keyspace": "dev_sync_gateway_sw1", "namespace": "default" }, { "#operator": "Filter", "condition": "((r.type) = \"user\")" }, { "#operator": "InitialProject", "result_terms": [ { "expr": "(r.id)" }, { "expr": "(r.fullName)" } ] }, { "#operator": "FinalProject" } ] } } ] }, { "#operator": "Limit", "expr": "50" } ] } ]

4

Hi @dipen_patel, we have fixed this issue with ORDER BY + LIMIT, and the fix will be available in the next release: http://review.couchbase.org/#/c/58086/

5

When will this patch be community and enterprise?

6

It is part of the next major release, codenamed Watson. There will be developer previews of Watson along the way, which will include this fix.