Custom indexing in Azure Cosmos Document DB doesn't work

Question

Ok, so the CosmosDb Collection has it's index policy set to consistent, automatic, has the default hash and range indexes AND we added a path to our own timestamp properties in order to sort by them.

I know the paths are correct, since I'm not able to order by them UNLESS I have them set. But:

When sorting by Cosmos built-in property _ts - the cost for an OrderBy query is like 20 RU/s. That's great. Now, when sorting by our OWN timestamp columns (we have two where one is a string timestamp, and the other is Unixbased number just as the built-in _ts column. This query costs 400 RU/s !???

Putting the new indexing rules enable us to query and order it, but the RUs are insane. Why is this and how do we address it?

I know that you couldn't change the indexing policy Ad Hoc earlier, but this has been addressed according to Microsoft.

EDIT: It is a simple collection, no partitioning is configured, and the query runs against this only collection, selects only one document (top 1).

SELECT top 1 * FROM c WHERE c.AllCompleted = true ORDER BY c.EndFetchDateTimeUtcUnix DESC vs

SELECT top 1 * FROM c WHERE c.AllCompleted = true ORDER BY c._ts DESC

The Index looking like this: { "indexingMode": "consistent", "automatic": true, "includedPaths": [ { "path": "/", "indexes": [ { "kind": "Hash", "dataType": "Number", "precision": 3 }, { "kind": "Hash", "dataType": "String", "precision": 3 } ] }, { "path": "/EndFetchDateTimeUtcUnix/?", "indexes": [ { "kind": "Range", "dataType": "Number", "precision": -1 }, { "kind": "Hash", "dataType": "String", "precision": 3 } ] } ], "excludedPaths": [] }

Can you include additional information including how many docs are returned from your custom query vs the order by _ts and whether or not this query is confined to a single partition? — Jesse Carter
Sure, it's only one. Edited the post above with the queries and the index. — imbageek

Samer Boshra Samer Boshra · Accepted Answer · 2017-06-13T16:20:11

It could be the case here that you're hitting index collision (multiple values map to the same index term).

To minimize the chances of collisions, and if the order-by item has known min/max values, you could add a filter on the order-by item to narrow down the range of index terms retrieved.

For instance,

SELECT * FROM c WHWE c.DateTime BETWEEN '2000-01-01T00:00:00.0000000Z' AND '3000-01-01T00:00:00.0000000Z' ORDER BY c.DateTime

Similarly you could apply the same technique to the numeric timestamp.

Custom indexing in Azure Cosmos Document DB doesn't work

2 Answers