Make COUNT() aware of indexes
Queries with COUNT() or other aggregates are very expensive and all appear to be scan driven vs. index driven.
I have 50k documents in collection, each has "Type" property with string value. The indexing policy is default.
The following simple query consumes 615 RUs:
SELECT VALUE COUNT(1) FROM x WHERE x.Type ='Content'
The result is 9738 records.
The query metrics are:
So what we see is that database reads all "Contents" and than counts them. But it's enough to just count the index entries not to read all "Contents".
This work is now completed and enabled on all new containers. We are in the process of upgrading existing containers to use this functionality. This should be complete across every Cosmos account by June 2020.
We will shortly be updating this doc to explicitly call out Count as using the index:
Stephan Dilly commented
This seems to be limited to the core api only? Count using mongodb api still consumes a ton of RUs like it’s not taking indices into consideration? Or how are we supposed to benefit from this?
Posted this also to the referenced docs page: https://github.com/MicrosoftDocs/azure-docs/issues/61205
Jesse White commented
Update on this or an ETA would be great.
Hassan Arteaga Rodriguez commented
Hi Guys: Any update about this issue ? COUNT function is very common in our queries.
+1 this is crazy
Antonio Puche commented
This causes huge issues for common scenarios. It's been in progress 8 months. When is it due to be released?
Pittsburgh DBA commented
I'm just going to say LOL at this. Why is COUNT() not index-aware out of the box? I currently have an absurd COUNT() trying to run on 3 billion documents. Please take care of this. RDBMS can do this is seconds.
Bharath M commented
+1. Does it have private availability? I'm willing to test in our app.
Jahmai Lay commented
Any update on this? Aggregates are so expensive it's making using cosmos prohibitive. At minimum Count should be RU cheap.
Cararo, Edinilson commented
Yes please in the mongodb API this is killing our through put!