CosmosDB MongoDB 3.6 fails sort() query with compounded index

0
votes

Newby MongoDB & CosmosDB user here, I've read the answer to this question How does MongoDB treat find().sort() queries with respect to single and compound indexes? and the offocial MongoDB docs and I believe my index creation mirrors that answer so I am leaning towards this being a CosmosDB issue but reading their documentation CosmosDB 3.6 supports compounded indexes as well, so I am at a loss right now.

I am able to run sort() queries like db.Videos.find().sort({"PublishedOn": 1}) from the mongo command line on a collection with an index created as db.Videos.createIndex({"PublishedOn": 1}) or db.Videos.createIndex({"PublishedOn": -1}).

And when I add a 'where' clause to the find like this db.Videos.find({"IsPinned": false}).sort({"PublishedOn": 1}) the above index still works.

However I now have document look ups which I want to avoid, so I drop the above single field index and create a compounded index like this db.Videos.createIndex({"IsPinned": 1, "PublishedOn": 1}) or db.Videos.createIndex({"PublishedOn": 1, "IsPinned": 1}) but now the query always fails with the error The index path corresponding to the specified order-by item is excluded..

Is this a limitation of CosmosDB or is my 'ordering' in the index bad?

1
azure-cosmosdb-mongoapi
Cosmos is an independent implementation that does not share any underlying server code with MongoDB, so you cannot infer expected behaviour based on what a MongoDB deployment supports. For example, the error The index path corresponding to the specified order-by item is excluded is specific to Cosmos' API. I expect the index on {"PublishedOn": 1, "IsPinned": 1} should be usable (or rather, would be used by MongoDB based on your query). It seems Cosmos may require different index definitions to support sorting. Perhaps the PublishedOn sort order in the index has to match the query? – Stennie
You are absolutely right that CosmosDB is not MongoDB, I hope I didn't infer that with my question. – Sauraus

1 Answers

1
votes

The issue with CosmosDB is that it expects all WHERE fields to be used in the GROUP BY clause as well in exactly the same order else it won't use the index.

Creating an index as db.Videos.createIndex({"IsPinned": 1, "PublishedOn": 1}) and then updating the query to be db.Videos.find({"IsPinned": false}).sort({"IsPinned": 1, "PublishedOn": 1}) works like a charm.

I inferred this from reading the CosmosDB documentation on indexing policies (https://docs.microsoft.com/en-us/azure/cosmos-db/index-policy) as the MongoDB documentation suddenly stops after the index creation (https://docs.microsoft.com/en-us/azure/cosmos-db/mongodb-indexing) section.