I want to query the distinct values of a certain property across all partitions in an Azure Cosmos DB container.
The particular property that is queried is indexed and has low cardinality, i.e. the number of distinct values are few (less than 100).
Although the query must run over all partitions I thought this kind of query would be efficient since it could be resolved directly from the index.
The SQL query is: (cnt is the low cardinality property I'm interested in)
SELECT DISTINCT c.cnt FROM c
Query stats (from Azure portal) reports:
- Request Charge: 13386.61 RUs
- Showing Results: 1 - 9
- Retrieved document count: 753161
- Retrieved document size: 676125621 bytes
- Output document count: 15
- Output document size: 455 bytes
- Index hit document count: 0
- Index lookup time: 0 ms
- Document load time: 8341.91 ms
- Query engine execution time: 637.4 ms
I'm getting 9 results which is the expected cardinality. But the stats seems to indicate that indexes weren't used at all - making it an expensive query.
The retrieved document count is 753 161. My first thought was that the query scan through all items, so I did a count of all items with the cnt property:
SELECT COUNT(c.cnt) AS TotalCount FROM c
But the results are:
[
{
"TotalCount": 3518847
}
]
This tells me that 753 161 out of 3 518 847 items (~21%) were scanned. That's great but makes me understand even less of what's going on.
Now to my question:
Can the SELECT DISTINCT query (cross partition) be written so that it makes (proper) use of indexes?
If not, please help me understand why the stats indicates a scan over 21% of all items with the queried property.