Using order by "param" DESC in N1QL is very slow (>5s)

query
n1ql

#1

Hi there,

We are using couchbase 4.0.0 v.
We have database with over 11 million document.
My index looks like this “CREATE INDEX transactionSearch ON zipt(lower(type),transactionType,createdOn,sourcePhoneNumber,destinationPhoneNumber,debitType,rateType,absoluteAmount) WHERE (lower(type) = “transaction”) USING GSI”.

When using “order by param DESC” in cbq it is very slow (>5sec). Is there any problems with ordering and performance I should be aware. If there is any solution for speeding things up please help.

Thank you,
Tomislav


#2

Hi,

As far as ORDER BY param DESC is concerned:
In 4.0.0, we’d fetch all the qualifying documents from KV, then SORT it.
In 4.1, if the Index has the param and query is covered, the query will get data just from the index, but sill have to sort it. This will improve the performance as well.

We don’t have a mechanism to do create an index with DESC order yet. Our team is aware of this requirement.


#3

Do you have any performance suggestions for the 4.0?
Is there any other way of working this out since this is quite of an “regular” thing to do :slight_smile:
The problem is we cannot use views because the data must be filtered and sorted based on multiple dynamic parameters
Any ideas how to overcome the performance issues?


#4

Hi there,

We have updated our couchbase to 4.5 version and we still have the same issue. Is there any workaround we can use? I am wondering is it possible to order elements by some column using view?

Thank you,
Tomislav


#5

Yes. ORDER BY ASC is very fast when you have a matching index, because the sort is avoided.

For ORDER BY DESC, you can convert it to an ORDER BY ASC as follows.

CREATE INDEX ON b( x );

SELECT ... ORDER BY x ASC;

Convert to these (for example if x is a number).

CREATE INDEX ON b( -x );

SELECT ... ORDER BY -x DESC;

This is a workaround. We are adding fast support for DESC in the next major release.


#6

Hi,

I’m still having this problem on Couchbase 4.6.1 - ORDER BY DESC queries are very slow, and using the above trick doesn’t seem to work.

Should this be fixed in 4.6.1?

Cheers


#7

hi @jackfarrelly, the DESC optimization is planned for 5.0.
Can you provide exact query/index etc details. Its not clear why the above technique is not working…
regards.


#8

@jackfarrelly, please share your exact queries and query plan.


#9

Hi guys, apologies, using ORDER BY -column ASC and having an index on -ASC does work, I misread the instructions.

Cheers


#10

I have this problem as well, where the index does not cover everything i need. However, i’m able to get what I want by doing the following:

select * from db USE KEYS (
select raw meta().id from db
where param1=x
order by param2 desc
limit 10
)

This will force the cover query and then do a fetch on the matched keys, where as doing a straight select will force the fetch which slows everything down.