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

159 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 →

3 comments

Attach a File
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

Feedback and Knowledge Base

(thinking…)
Hosted by UserVoice