Performance Issue - Large Number of Documents

Couchbase Server
1

Hello,

We are using Community Edition 6.0.0 build 1693 ‧ IPv4.
We have a large number of documents about 23 million of them

Below mentioned is an index that we have created
CREATE INDEX indexa ON apbucket(segment,status,LastUpdate DESC)

Here is the n1ql which we are using

SELECT APID,LastUpdate FROM apbucket WHERE segment IN [“ABC”] AND status=“ACT” AND LastUpdate BETWEEN
“1970-01-01T00:00Z[UTC]” AND “2020-06-15T15:54:42.359Z[UTC]” OFFSET 12000000 LIMIT 1000

Issue that we have is : we see a severe drop in performance and also timeouts as we increase the Offset value.
Query performs well with smaller values
As we increase the offset value to 1,100,1000,10000 ,100000,1000000,10000000 we see a drop in performance and timeouts.

We have seen an suggestion listed in this blog about use of meta().id for these kind of scenarios.

we would like our consumers to make multiple calls to fetch this information in parallel and having stateful transactions to have context of meta().id maintained is not really a choice that we were willing to consider

Are there any other possibilities for solving this issue with performance.

Regards
Srinivas

2

When OFFSET is specified there no easy way to skip unless it process those and skip.

3

@srinivas, Your query must process all the values from the beginning through OFFSET + LIMIT. It is expected that a large value of OFFSET will perform slowly, since so much data must be processed.

The blog post you reference suggests using an index and ORDER BY on the same keys. Have you tried that? In that case the query engine could use the index to support OFFSET and LIMIT, which would be much faster than processing all the individual documents.