Does Azure Cosmos DB (Mongo API) utilize compound indexes or memory during sorting?

Question

According to the Cosmos DB Mongo API (vCore) documentation, an existing indexing limit is stated as follows:

Sorting is done in memory and doesn't push down to the index.

Should this be understood to mean that indexes do not participate in sorting the result set, only in filtering?

If that's the case, the concern is that sorting cannot be done effectively on collections sized in GiBs-TiBs.

However, there is also another piece of documentation saying that:

Compound indexes improve database performance by allowing efficient querying and sorting based on multiple fields within documents.

So the question is, would it be safe to utilize compound indexes for filtering and sorting on large collections, or could it result in excessive usage of memory?

Answer 1

Hi @Dmytro Kucheryavenko,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

According to the Cosmos DB Mongo API (vCore) documentation, sorting is done in memory and doesn't push down to the index.

This means that indexes do not participate in sorting the result set, only in filtering. However, compound indexes can still improve database performance by allowing efficient querying and sorting based on multiple fields within documents.

So, the question is, would it be safe to utilize compound indexes for filtering and sorting on large collections, or could it result in excessive usage of memory?

In the API for MongoDB, compound indexes are required if your query needs the ability to sort on multiple fields at once. For queries with multiple filters that don't need to sort, create multiple single field indexes instead of a compound index to save on indexing costs.

A compound index or single field indexes for each field in the compound index results in the same performance for filtering in queries.

For more information, please refer the document: https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/indexing#compound-indexes-mongodb-server-version-36.

To optimize sorting on large collections, you can use compound indexes to sort on multiple fields at once. Additionally, you can use pagination to limit the number of results returned and reduce the amount of data that needs to be sorted.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.