N1QL vs View Performance Issue

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.

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?

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" } ] } ]

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/

When will this patch be community and enterprise?

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.