How can we improve Azure Cosmos DB?

← Azure Cosmos DB

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:
RetrievedDocumentCount: 9738
RetrievedDocumentSize: 69506571
OutputDocumentCount: 1
OutputDocumentSize: 53
IndexHitDocumentCount: 0
IndexHitRatio: 0

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".

279 votes
Vote
Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
You have left! (?) (thinking…)
Oleg Gliznutsa shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
completed  ·  AdminAzure Cosmos DB Team (Product Manager, Microsoft Azure) responded  · 

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:
https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-aggregates

Thanks.

Show previous admin responses (1)

11 comments

Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment

Azure Cosmos DB: SQL API

Categories

Feedback and Knowledge Base

(thinking…)
Hosted by UserVoice