DocumentDB adding ORDER BY clause uses excessive RUs

Question

I have a partitioned collection with about 400k documents in a particular partition. Ideally this would be more distributed, but I need to deal with all the documents in the same partition for transaction considerations. I have a query which includes the partition key and the document id, which returns quickly with 2.58 RUs of usage.

This query is dynamic and potentially could be constructed to have an IN clause to search for multiple document ids. As such I added an ORDER BY to ensure the results were in a consistent order, adding the clause however caused the RUs to skyrocket to almost 6000! Given that the WHERE clause should be filtering down the results to a handful before sorting, I was surprised by these results. It almost seems like it's applying the ORDER BY before the WHERE clause, which must not be correct. Is there something under the covers with the ORDER BY clause that would explain this behavior?

Example document:

{
"DocumentType": "InventoryRecord", (PartitionKey, String)
"id": "7867f600-c011-85c0-80f2-c44d1cf09f36", (DocDB assigned GUID, stored as string)
"ItemNumber": "123345", (String)
"ItemName": "Item1" (String)
}

With a Query looking like this:

SELECT * FROM c where c.DocumentType = 'InventoryRecord'
and c.id = '7867f600-c011-85c0-80f2-c44d1cf09f36'
order by c.ItemNumber

Answer 1

You should at least put a range index to ItemNumber. This should ensure, there is a ordering as expected. The addition in your indexing policy this would look like

{ "path": "/ItemNumber/?", "indexes": [ { "kind": "Range", "dataType": "String", "precision": -1 } ] }