Cosmos DocumentDb: Inefficient ORDER BY

Question

I'm doing some early trials on Cosmos, and populated a table with a set of DTOs. While some simple WHERE queries seem to return quite quickly, others are horribly inefficient. A simple COUNT(1) from c took several secons and used over 10K request units. Even worse, doing a little experiment with ordering also was very discouraging. Here's my query

SELECT TOP 20  c.Foo, c.Location from c
ORDER BY c.Location.Position.Latitude DESC

My collection (if the count was correct, I got super weird results running it while populating the DB, but that's another issue) contains about 300K DTOs. The above query ran for about 30 seconds (I currently have the DB configured to perform with 4K RU/s), and ate 87453.439 RUs with 6 round trips. Obviously, that's a no-go.

According to the documentation, the numeric Latitute property should be indexed, so I'm not sure it's me screwing up here, or the reality didn't really catch up with the marketing here ;)

Any idea on why this doesn't perform properly? Thanks for your advice!

Here's a document as returned:

  {
    "Id": "y-139",
    "Location": {
      "Position": {
        "Latitude": 47.3796977,
        "Longitude": 8.523499
      },
      "Name": "Restaurant Eichhörnli",
      "Details": "Nietengasse 16, 8004 Zürich, Switzerland"
    },
    "TimeWindow": {
      "ReferenceTime": "2017-07-01T15:00:00",
      "ReferenceTimeUtc": "2017-07-01T15:00:00+02:00",
      "Direction": 0,
      "Minutes": 45
    }
  }

The DB/collection I use is just the default one that can be created for the ToDo application from within the Azure portal. This apparently created the following indexing policy:

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

Could you add an example of your document (or only its relevant part)? — Mikhail Shilkov
Hi Mikhail! I updated the question with some returned JSON. The stored DTO contains some other properties, but those should be irrelevant. — Philipp Sumi
Phillip, how many RU's did you provision this collection with and did you choose a value to use as your partition key when it was created? — Jesse Carter
@JesseCarter I gave it 4K RU/s to start with, with 10K being the max, so I'd expect more than decent performance. On the other hand, with the operation eating up nearly 90K RUs, this has to be slow on any setup. The collection is just the one that can be created in Azure's quickstart section for their ToDo app, so I wouldn't know about that. It's only a single partition DB for sure. — Philipp Sumi

Philipp Sumi Philipp Sumi · Accepted Answer · 2017-12-18T08:05:46

Update as of Dec 2017:

I revisited my unchanged database and ran the same query again. This time, it's fast and instead of > 87000 RUs, it eats up around 6 RUs. Bottom line: It appears there was something very, very wrong with Cosmos DB, but whatever it was, it seems to be gone.

Cosmos DocumentDb: Inefficient ORDER BY

1 Answers