Couchbase Server CE 'get newest results' slow query

Community Edition cannot use index to satisfy following N1QL query:

SELECT * FROM sensorbucket WHERE type = 'data' ORDER BY timestamp DESC LIMIT 50;

Given the document structure:
{ “id” : “123”, “type” : “data”, “value” : “some value”, “timestamp” : 1478109980000}

and the fact that ‘sensorbucket’ contains millions of documents with ‘type’ : ‘data’, I have to get newest n results very fast. So sorting in memory is not viable solution here.

Is there any solution for this problem (getting newest results fast) in Community Edition (at this point Enterprise Edition is not an option)? Like using views or some other Couchbase feature?

EDIT: Can anyone, please, at least say if there is a different solution? Even ‘no’ is an answer. Documentation is quite vague about index usage.

Hi @milansm

It looks like to me that you should first try indexing the ‘type’ field.

Something like

CREATE INDEX ON `sensorbucket` (type);

See if that has any effect. You can also use EXPLAIN to see which index your query is using.

Sorry it took so long to reply. I would recommend you post future N1QL questions in the N1QL forum, you’re more likely to get a faster response from the N1QL team.

Hi @matthew.groves, thanks for trying to help.
Let me rephrase the question.

What I’d find out is that the sort order cannot be obtained from the index in Couchbase Server Community Edition. That’s why for sufficiently large number of documents in the database, the request times out, because sorting is performed in memory.

I created following index:

create index type_created_desc on sensorbucket(type,-created) using GSI;

And when I try:

select * from sensorbucket where type = 'data' order by created desc limit 50;

Couchbase uses the index to filter the documents with type = 'data' but the sorting part of the query is performed in memory.

So, my question is actually threefold:

  1. Is it true that sort order cannot be obtained from the index in Couchbase Server Community Edition, or I missed something? Documentation does not mention creating indexes in descending order at all.
  2. If that’s true, is there any other way to get 50 newest results fast? Maybe using some other feature, like views or something? For example, on my laptop it takes ~2 seconds for Couchbase to fetch the results when there is 40k of documents. With MongoDB I get the results for the same number of documents in less than 1 millisecond. I expect similar performance from the Couchbase.
  3. If that is not possible in Couchbase Server Community Edition at all, is it possible in Couchbase Enterprise Edition? Upgrade to EE might be an option after all, but I need a clear answer that Couchbase CE cannot solve the issue of fetching 50 newest documents fast.

This is general Couchbase Server question, I just want newest 50 results returned fast. If I can use an index and N1QL for that, that’s great, if not I would like to know if there is the other way. That’s why I did not post the question in N1QL forum.

@milansm,

I think @geraldss might have a better idea from the N1QL point of view, so I’m tagging him. Based on this ticket on couchbase.org, I think you might be seeing an issue that was fixed in Couchbase 4.5 (which is currently available as an Enterprise release only, but will eventually be a Community release). Feel free to try out Enterprise Edition on your laptop, you’ll only need a license when you go into production.

Another option besides N1QL is a map/reduce view. Maybe. This is an older feature of Couchbase Server and many of the use cases are replaced by N1QL, but it is still an option, and is probably better for high-write applications. Sorting is going to make that more complex to write, however.

Hi @vsr1, please provide the N1QL answer for this. What is the answer for 4.1.1 CE, and what is the answer for 4.6.

Thanks.

Hi @milansm,

You can create partition negative functional index on timestamp.

CREATE INDEX ix1 on sensorbucket(-timestamp) WHERE type = 'data';

In 4.5 and above you can use the following query, Query ORDER BY matches with index keys and will use Index Order.

SELECT * FROM sensorbucket WHERE type = 'data' and -timestamp IS NOT MISSING ORDER BY -timestamp LIMIT 50;

In 4.1.1 Just skip the query order by clause that way the data returned will follow the index order (query needs to use the index ix1, You can verify the results).

     SELECT * FROM sensorbucket USE INDEX(ix1) WHERE type = 'data' and -timestamp IS NOT MISSING LIMIT 50;

    SELECT s.* FROM (SELECT RAW META().id FROM sensorbucket USE INDEX(ix1) WHERE type = 'data' and -timestamp IS NOT MISSING LIMIT 50) qa INNER JOIN sensorbucket s ON KEYS qa ORDER BY s.timesatmp DESC LIMIT 50;
1 Like

The index keys order is type,-created and query order BY is on created desc. query order by list doesn’t match with index keys list left to right.

Your query has equality predicate in 4.5.x if you modify the query as follows it will use index order.
select * from sensorbucket where type = 'data' order by type, -created limit 50;

In 4.6.0 the following query also uses index order.
select * from sensorbucket where type = 'data' order by -created limit 50;

1 Like

Hi @vsr1, thanks for helping out!

It seems that it works now. On 4.1.1 CE I created the index:
create index ix1 on sensorbucket(owner,type,-created) using GSI;
and the query:
select * from sensorbucket where owner = 'alice' and type = 'data' limit 10;
returns what seems to be correctly sorted results in ~30ms. Which is not as fast as other databases, but a lot faster than before. It’s usable now.

I also tried out the solution for 4.5 version and it works fine as well.

This is very basic requirement and I wonder why relevant information is not available in the official documentation. I had to spend a lot of time trying to find out if it is possible at all to created indexes on fields in descending order and what the syntax is. Also, in this tweet from the official N1QL account it’s stated that index order can be obtained from the index in version 4.5 and above.

Did I miss some part of the docs or the info about index usage (descending, order by, etc.) is not available officially (in the documentation)?

1 Like

Hi @prasad,

Friendly user suggestion for documentation.