CosmosDB Composite Index Unused by a query

[SafwanHussain]

Hi,

I am creating a composite Index on a container to cater to queries with multiple filters ex:
SELECT value count(1) FROM c where c.uId="12445" and c.la>= 1585076195

Details:
uId is the partitionKey

Custom Index :

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

Note : After updating the Index the same query consumes more RUs also the Index hit document count is 0 indicating that composite index is unused.
Also I observed that the index size on the container in the Metric section ,doesn't change after updating the Indexing policy, it remains same as it was for the default Index even after a day or so, it actually increased few MBs. With my understanding it should change right as we are now excluding everything from indexing and referencing less paths with out custom indexing policy.

Can someone tell me why the query is consuming more RUs after updating the Index and the Composite index not being used by the query also no change in the Index size

[tamusjroyce]

This is probably beyond an issue with the Powershell module. As this module helps build Rest API Cosmos calls. This seems like an issue with the Rest API itself (or a missing header in the request).

That being said. What happens when you append ORDER BY?

SELECT value count(1) FROM c where c.uId="12445" and c.la>= 1585076195 ORDER BY c.uId ASC, c.la ASC

1. Powershell Module
2. Data Explorer

You might use Fiddler4 or other proxy to grab the Cosmos Request. Adding -Verbose in the places supported might help you diagnose this too.

(Just learning here - reply or PM me that this isn't the case and I can remove this comment since it is irrelevant - don't want it to look like you've gotten help if you haven't)

[PlagueHO]

As @tamusjroyce mentions, this module is really just a wrapper for the REST APIs.

Composite Indexes only affect the ORDER BY clause and enable sorting by multiple properties when querying documents (see https://docs.microsoft.com/en-us/azure/cosmos-db/index-policy#composite-indexes). So you only need to use composite indexes when you want to be able to specify a query like the one @tamusjroyce shared.

Also, when modifying indexes, I would expect it to take some time (depending on the number of documents in the collection) for the index to update. I'm not certain of the amount of time though.

Sidenote: The Cosmos DB SDK can determine if the index is still being updated. It may also be possible for this module to also detect it, but I'll have to identify how the SDK does it.

[PlagueHO]

You could also ask over here in the Cosmos DB forums: https://social.msdn.microsoft.com/Forums/en-US/home?forum=azurecosmosdb

[PlagueHO]

Actually, I've found out how to get the state of the index transformation. It is returned in the x-ms-documentdb-collection-index-transformation-progress header when calling Get-CosmosDbCollection. I'm adding this to the documentation as well as to the examples.