Cosmos DB Advisor recommending Composite Indexes but on which fields?

Question

The Cosmos DB Advisor is giving me the following recommendation:

Your Azure Cosmos DB containers are running ORDER BY queries incurring high Request Unit (RU) charges. It is recommended to add composite indexes to your containers' indexing policy to improve the RU consumption and decrease the latency of these queries

I believe you have to explicitly add composite indexes for the requisite fields. However it's not obvious how to determine which fields need to be indexes.

Would appreciate any assistance.

Answer 1

Hi Andreas Cambitsis,

When you add a composite index, the query will utilize existing range indexes until the new composite index addition is complete. Therefore, when you add a composite index, you may not immediately observe performance improvements.

Please find documentation related to Composite Index as below,
https://learn.microsoft.com/en-us/azure/cosmos-db/index-policy#composite-indexes

Composite indexing policy examples,

https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/how-to-manage-indexing-policy?tabs=dotnetv3%2Cpythonv3#composite-index

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

Answer 2

Hi, @Andreas Cambitsis Thanks for posting your question in the Microsoft Q&A forum.

The queries that are run on the container will determine which composite indexes may help improve the performance… These can be properties that use equality or range filters, or that follow the ORDER BY clause of the queries.

There is specific guidance in the documentation here

Regards

Geetha

Answer 3

Thanks for the response . I wasn't sufficiently clear in my initial question: It wasn't obvious how to view the queries running against the container in order to determine the problematic ones, and in turn which fields to use.
I have subsequently enabled full-text query and log analytics diagnostic logging. I am using guidance from this article to determine which queries I should focus on.