Same query on same data consumes as much as double the RUs between calls

0
votes

We have a cosmos db container where documents are inserted (append-only). We are seeing the same query consume wildly varying RUs even though the number of matching documents did not change. I am able to reproduce in Data Explorer.

Query:

select * from c where c.Id=<someId> and c.Version > <someVersion> order by c.Version asc

PartitionKeyPath = /Id

When it's executed successively in DataExplorer, I get the following query statistics:

  1. Request Charge = 4857.38 RUs, Index lookup time = 9562.75 ms, Retrieved document count = 77
  2. Request Charge = 1900.79 RUs, Index lookup time = 466.72 ms, Retrieved document count = 77
  3. Request Charge = 1878.25 RUs, Index lookup time = 548.80 ms, Retrieved document count = 77

Note the varying RUs and index lookup times (see actual screen shots with remaining data). Our logs show the same query taking upward of 7964 RUs and 20 seconds!

Also note then when I remove the "order by" clause, I start getting the same RUs on successive executions.

Per documentation: cosmos db guarantees that the same query on the same data always costs the same number of RUs on repeated executions. Why are we seeing those variations?

screen capture from Data Explorer - attempt 1 screen capture from Data Explorer - attempt 2

1
azure-cosmosdb

1 Answers

0
votes

I can answer first as why it's taking so long. The reason is you need to add a composite index. You should always explore adding a composite index to optimize ORDER BY queries. They can have a huge impact.

In regards to the guarantee in the RU section of our docs, it is general guidance that is true 99% of the time but we can’t guarantee it. We will update our docs on this to be more concise.

AS for why the RU/s was different, based upon the query metrics you shared, the cheaper 1,878 RU query had a 548 ms index lookup time, while the more expensive 4,857 RU query had a 9+ second index lookup time. This is probably a hint to why the RU difference is so high but it's not possible to say for sure why without activity id's on the operations.

The query engine has a lot of heuristics it uses during execution and, in some cases, a small change could have a significant impact on the query results. Even things like throttling could force a query to be split into an inconsistent number of pages, impacting the RU charge. This is rare, but possible.