Why N1qlMetrics can be EMPTY_METRICS

SQL++
,
1

Hi, I’m using the Java SDK and I have a query that fetches some data from a bucket using the WHERE clause, and then applies an ORDER, LIMIT and an OFFSET which works perfectly fine.

Now what I also need to do is figure out how many records in total did I get prior applying the limit/offset. In different posts, I saw that I can just do a COUNT query with my WHERE clause. However, this would require me to do a second query which is not great.

I started looking into what I have in N1qlQueryResult and I saw it gives me N1qlMetrics which in turn has sortCount. Looking at the Couchbase docs, I can see it has exactly what I need, the number of records returned from the WHERE query, prior applying the limits.

HOWEVER:

  1. I see the bellow in the Java doc, when would that happen and the server would omit the metrics? Is it when it errors out and I actually don’t get any results back ?

/ **
* A class that represents N1QL metrics.
*
* Note that the server could omit the metrics or part of the metrics,
* in which case an {@link #EMPTY_METRICS} will be returned.

  1. Does this mean that I cannot use the N1qlMetrics in a reliable way and I should fall back to what I see as inefficient and doing 2 queries with a COUNT ?
2

You should never depened on sortCount. It is not reliable. See https://stackoverflow.com/questions/63569822/couchbase-metrics-sortcount-always-zero-n1ql-net-sdk/63570767#63570767 for reason. In addition some times we do TOP n sort.

3

Hey, thanks for the details !

1 follow up question actually, based on your response in stackoverflow. Is it still a concern if the ORDER does not use an index ?

4

No. If it can’t use index order it does explicit sort. In that case it must produce all qualified records and do sort that may take time.

https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/ explains