Running inefficient SQL queries against Cosmos DB can quickly consume your allocated throughput budget. Understanding index mechanics and query patterns allows you to write lean, low-cost queries.
Indexing Policy Customization
By default, Cosmos DB automatically indexes every path in every item. While convenient for prototyping, indexing unnecessary strings and arrays inflates write latency and storage costs. Excluding heavy fields significantly lowers operational costs.
{
"indexingMode": "consistent",
"automatic": true,
"includedPaths": [
{
"path": "/*"
}
],
"excludedPaths": [
{
"path": "/\"description\"/?"
},
{
"path": "/\"rawPayload\"/?"
}
]
}Key Optimization Rules
- Avoid Cross-Partition Queries: Always include the partition key in your
WHEREclause when possible to prevent queries from fanning out to every physical partition. - Project Specific Fields: Replace
SELECT *with targeted field projections (e.g.,SELECT c.id, c.status FROM c) to minimize serialization and bandwidth costs. - Use Composite Indexes: Create composite indexes for queries containing multiple
ORDER BYor filter clauses across different properties.
Cosmos DB query costs often come down to how much data a query has to examine, not just how many rows it returns. A few habits—especially using the right partition key and checking query metrics—can make a noticeable difference to both request units (RUs) and latency.
Start with the access pattern
If you know an item’s
idand partition key, use a point read rather than a SQL query. Point reads are usually the most efficient way to retrieve a single item. For queries, include a filter on the partition key whenever the request can be scoped to one partition. A query that must search across partitions can still be appropriate, but it generally does more work.Choose a partition key that fits the application’s common reads and writes, and check for hot partitions. A query change cannot fix a workload that sends too much traffic to one partition.
Measure before changing indexes
Check the request charge and latency for representative queries in your SDK diagnostics or Azure monitoring. Query metrics can help show whether Cosmos DB retrieved many documents to return only a few. Index metrics can also reveal whether a query is using the index as expected.
Pay attention to the gap between retrieved and output document counts. A large gap can point to a broad filter or a query that is examining more data than the application needs. Test changes against realistic data; a query that performs well on a small development container may behave differently at production scale.
Keep queries focused
Return only the fields the caller needs instead of whole documents. For example:
Projection reduces response size and client-side work. Its effect on RU charge depends on the query and data shape, so verify the change with request-charge measurements rather than assuming it will always lower RUs.
For paged results, use continuation tokens instead of repeatedly skipping earlier rows with a large
OFFSET. Deep offsets can force the query to process rows that the application discards.Review the indexing policy against real queries
Cosmos DB indexes included paths by default. That is convenient, but indexing every property can add storage and write costs, especially for large documents or arrays. If your workload is well understood, consider excluding paths that are not queried, and add the indexes your actual filters and sort operations need. An index-policy change can make existing queries slower or unsupported, so test it before deploying.
Queries that sort by multiple properties may need a matching composite index. Use index metrics and the error details from Cosmos DB to guide that decision rather than adding composite indexes speculatively.
When comparing changes, record both RU charge and elapsed time under a representative workload. A lower RU charge is useful, but it is only part of the picture if the query still has poor latency or creates a hot partition.
Why RU Economics Matter
Azure Cosmos DB bills you per Request Unit. A single inefficient query can burn through your monthly allowance in minutes. Understanding how RU consumption works is the first step toward keeping costs predictable.
The RU Breakdown
Every query consumes read RUs if it touches items, plus write RUs if it modifies them. The total depends on the logical data size processed, not the physical size on disk. Cosmos DB scans only the partition key range specified in your filter, so narrow filters save RU. Broad scans across partitions are expensive.
Practical Tuning Steps
Start with your most frequent queries. Check the metrics in Azure Portal or via the SDK to see average RU per operation. If a query consistently spikes, look for missing or misaligned indexes.
Index Strategy
Query Rewrites
Replace client-side filtering with server-side
WHEREclauses. Pull only the columns you need instead ofSELECT *. Use parameterized queries to benefit from plan caching and reduce RU overhead.Partitioning
Design your partition key so related data lands in the same logical partition. This keeps cross-partition queries out of your workload. If your key causes hotspots, redistribute data by using a hash suffix or a composite key.
Monitoring and Iterating
Set up alerts on RU consumption per second. Run the Azure Cosmos DB emulator locally to test changes without burning production RUs. Review your RU graph weekly; small regressions compound quickly.
There is no magic bullet. Tuning is iterative. Measure, change one variable, measure again, and keep what works.