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".
Oleg Gliznutsa
shared this idea
We have started work on this feature. Will update here when this becomes generally available.
Thank you for your request and votes.
1 comment
-
Ryan
commented
Yes please in the mongodb API this is killing our through put!