Azure Cosmos DB Add Composite Index for Array of String

I am trying to add a new composite index to do multiple fields search.

I would like to know thing to consider while adding a new Composite index and will it work for array string?

Sample Cosmos Document

{         "id": "ed78b9b5-764b-4ebc-a4f2-6b764679",         "OrderReference": "X000011380",         "SetReferences": [             "000066474884"         ],         "TransactionReference": "ed78b9b5-764b-4ebc-6b7644f06679",         "TransactionType": "Debit",         "Amount": 73.65,         "Currency": "USD",         "BrandCode": "TestBrand",         "PartitionKey": "Test-21052020-255",         "SettlementDateTime": "2020-05-21T04:35:35.133Z",         "ReasonCode": "TestReason",         "IsProcessed": true,            } 

My Existing index policy

{     "indexingMode": "consistent",     "automatic": true,     "includedPaths": [         {             "path": "/PartitionKey/?"         },         {             "path": "/BrandCode/?"         }     ],     "excludedPaths": [         {             "path": "/*"         },         {             "path": "/\"_etag\"/?"         }     ],     "compositeIndexes": [         [             {                 "path": "/PartitionKey",                 "order": "ascending"             },             {                 "path": "/IsProcessed",                 "order": "ascending"             }         ]     ] } 

To fetch data from Array of string SettlementReferences, IsProcessed, ReasonCode.

SELECT * FROM c WHERE ARRAY_CONTAINS(c.SettlementReferences, '00884') and c.IsProcessed = true and c.ReasonCode = 'TestReason' 

I am planning to add the following policy

{     "indexingMode": "consistent",     "automatic": true,     "includedPaths": [         {             "path": "/PartitionKey/?"         },         {             "path": "/BrandCode/?"         }     ],     "excludedPaths": [         {             "path": "/*"         },         {             "path": "/\"_etag\"/?"         }     ],     "compositeIndexes": [         [             {                 "path": "/PartitionKey",                 "order": "ascending"             },             {                 "path": "/IsProcessed",                 "order": "ascending"             }         ],         [             {                 "path": "/SettlementReferences",                 "order": "ascending"             },             {                 "path": "/IsProcessed",                 "order": "ascending"             },             {                 "path": "/ReasonCode",                 "order": "ascending"             }         ]     ] } 

Please let me know if this change is sufficient?

Moreover, I tried to compare RU’s before and after the change. I don’t see any massive difference, both coming around 133.56 Rus.

Is there anything more I need to consider for optimized performance?

Asked on August 31, 2020 in .NET,   ASP.net.
Add Comment
1 Answer(s)

Composite Indexes will not help with this query and overall don’t have any impact on equality statements. They are useful when doing order by’s in your queries. This is why you don’t see any RU/s reduction in your query. However, you will notice increased RU/s on writes.

If you want to improve your query performance you should add any properties in your where clauses into the "includedPaths" in your index policy.

Another thing to point out too is generally it is a best practice to by default index everything and selectively add properties to excludedPaths. This way, if your schema changes it will be indexed automatically without having to rebuilt your index.

Answered on August 31, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.