How to retrieve Indexing Metrics for your Cosmos DB queries?

     

    Azure Cosmos DB indexes all properties by default. It is great to not worry about which property should be indexed in your database but after a while, you realize that the index file gets really large and you pay for the storage separately. Storage is cheap, but why do you want to pay for something you might not be using.  Also, Don't forget Cosmos DB needs to change the index file for each insert, update and delete so if you have to write-heavy application, indexing everything might be slowing down your inserts and updates.

    You might need Composite Indexes to make your queries more efficient, Cosmos DB does not create any Composite Indexes for you. You need to figure out which properties should have composite indexes then you need to change the indexing policy file to create them. 

    Indexing Metrics comes to your help when you need help with indexing policy. It tells you which indexes the current query uses and it gives you hints about what other indexes you should create to make the query work faster/cheaper. Like many other features of Cosmos DB, you need to write code by using SDK to see Indexing Metrics. The following example shows how to enable Indexing Metrics for your queries.

    string sqlQueryText = "SELECT * FROM c WHERE c.OwnerUserId = 1 and c.ViewCount > 20";
    Container container = cosmosClient.GetContainer("Stackoverflow", "Posts");
    QueryDefinition query = new QueryDefinition(sqlQueryText);
    var resultSetIterator = container.GetItemQueryIterator<StackOverflowPost>(
      query, requestOptions: new QueryRequestOptions{
          PopulateIndexMetrics = true
      });
    FeedResponse<StackOverflowPost> response = null;
    while (resultSetIterator.HasMoreResults)
    {
      response = resultSetIterator.ReadNextAsync().Result;
      Console.WriteLine(response.IndexMetrics);
    }
    

This code returns the following result in the console.

    Cosmos Db tells us that it uses OwnerUserId and ViewCount indexes for this query. You don't want to remove any of these indexes. Cosmos Db suggests we to creating create a composite index on these properties too. If the cardinality of OwnerUserId and viewCount, you might see large savings by creating Composite Index for these properties. In my case, I don't really have that much data in my database, and creating a composite index on these fields is not going to save me anything, probably it will cost me more storage. Don't forget, these are potential indexes, they are just suggestions. Try to create it in a non-production environment first and test them before you try to create them in your production environment.

     I have been working on a side project for the Azure Cosmos DB Community named Cosmos DB Studio. It is a VSCode Extension and it lets you query SQL API from VsCode for FREE. It supports Indexing Metrics and it makes your life easier to see Indexing Metrics for your queries. Here is a screenshot of the Indexing Metrics results for the same query.

    You can download this extension directly from the VsCode or from this link.