Cosmos db sporadic 'requires a range index' exception when executing .net core client CreateDocumentQuery<T>

Question

I'm getting sporadic/inconsistent behaviour from CosmosDb when executing queries from the .NET client.

return this.client.CreateDocumentQuery<T>(this.documentCollectionUri, options).Where(t => ...).Orderby(t => ...);

It's a simple query with a where and order by clause. I have done no explicit configuration of anything like indexes on this collection, it's the 'out of the box' configuration with a particular partition key specified.

Unfortunately I cannot see any pattern with what causes this to occur. The exact same code can execute dozens of times without fault but randomly it will throw this exception:

System.AggregateException occurred HResult=0x80131500 Message=One or more errors occurred. Source= StackTrace: at System.Threading.Tasks.Task1.GetResultCore(Boolean waitCompletionNotification) at Microsoft.Azure.Documents.Linq.DocumentQuery1.d__31.MoveNext() at System.Collections.Generic.List1.AddEnumerable(IEnumerable1 enumerable) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at CompassDomain.Services.TravelRequestService.GetTravelRequests(TravelRequestSearchModel search) in C:\Users\Matty\source\repos\Compass\CompassDomain\Services\TravelRequestService.cs:line 542 at Compass.Controllers.TravelRequestController.Search(TravelRequestSearchModel search) in C:\Users\Matty\source\repos\Compass\Compass\Controllers\TravelRequestController.cs:line 242 at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__12.MoveNext()

Inner Exception 1: DocumentClientException: Message: {"Errors":["Order-by item requires a range index to be defined on the corresponding index path."]} ActivityId: 7307a098-6158-4437-be0d-cfbd6d1f8d23, Request URI: /apps/d6be8788-3942-411f-8d07-38c2e713953f/services/62737d1f-9d78-40ad-bf02-3852da0fa6ba/partitions/287201ef-5e4c-4276-9145-230e01c38d3d/replicas/131555532121431180s, RequestStats: ResponseTime: 2017-11-30T10:26:53.0446055Z, StoreReadResult: StorePhysicalAddress: rntbd://10.0.0.216:16700/apps/d6be8788-3942-411f-8d07-38c2e713953f/services/62737d1f-9d78-40ad-bf02-3852da0fa6ba/partitions/287201ef-5e4c-4276-9145-230e01c38d3d/replicas/131555532121431180s, LSN: 160, GlobalCommittedLsn: 159, PartitionKeyRangeId: 0, IsValid: True, StatusCode: 0, IsGone: False, IsNotFound: False, RequestCharge: 1, ItemLSN: -1, ResourceType: Document, OperationType: Query , SDK: Microsoft.Azure.Documents.Common/1.17.101.1

Any thoughts or pointers on what might be the cause, or even just the best path to report this issue?

FYI, this is the indexing policy on the collection, it's unchanged from the default configuration:

{
  "indexingMode": "consistent",
  "automatic": true,
  "includedPaths": [
    {
      "path": "/*",
      "indexes": [
        {
          "kind": "Range",
          "dataType": "Number",
          "precision": -1
        },
        {
          "kind": "Hash",
          "dataType": "String",
          "precision": 3
        }
      ]
    }
  ],
  "excludedPaths": []
}

I've not explicitly specified or configured anything around indexing on the collection. — MIP1983
– MIP1983, Commented Nov 30, 2017 at 10:54

Nadeem Shaikh · Accepted Answer · 2017-11-30 10:57:26Z

1

According to your inner-exception I will suggest you to make changes to indexing policy of your collection because with "Hash" kind of index on your collection you will not be able to use order by clause so change it to range as an example below and then check

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
       {
            "path": "/*",
            "indexes": [
                {
                    "kind": "Range",
                    "dataType": "String",
                    "precision": -1
                },
                {
                    "kind": "Range",
                    "dataType": "Number",
                    "precision": -1
                }
            ]
        }
    ],
    "excludedPaths": []
}

answered Nov 30, 2017 at 10:57

Nadeem Shaikh

3626 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MIP1983 Over a year ago

I can potentially make this change, but I don't really understand what it is, why I need to do it or the impact of doing it. I am presently able to use an order by clause in my queries, and the majority of the time it works just fine. Why would the default indexing policy of CosmosDb cause sporadic errors?

Nadeem Shaikh Over a year ago

Well, Cosmos DB is specially built for big data and it provides range indexing on numbers by default. Since we know that data in Cosmos DB is stored in JSON form they had provided the flexibility to end user to end-user to use indexing according to his own will as per his requirement and performance aspect. In Cosmos you can sort query results by using any string, numeric, or Boolean property in your JSON documents. I dont know the exact reason but may be it is upon the algorithm they are using for data filtering and sorting. And yes, it is not an error it is the flexibility they had provided.

Nadeem Shaikh Over a year ago

For more details you can refer this - tutorialspoint.com/documentdb/documentdb_indexing_records.htm

MIP1983 Over a year ago

As far as I can tell, it won't let me specify both a hash and range index on dataType 'String' together. Do you know whether if I change from hash to range, if it will hurt performance of 'where' queries? I think logically what you're saying makes sense based on the exception, but I'm still baffled why the exception is only occuring like 1/50 of the time for the same query.

Nadeem Shaikh Over a year ago

You can specify different indexing for your each document as well. If your data is very big then you should consider indexing policy for each document if it need or not. In my case I had used range for almost my all documents but I had not reached the threshold. It can affect the performance only if data is very very very big. It completely depends upon your project requirement. There are various aspect through which you can improve performance like throughput, document size,etc. if you are needing sorting desperately for all your documents.

Collectives™ on Stack Overflow

Cosmos db sporadic 'requires a range index' exception when executing .net core client CreateDocumentQuery<T>

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related