Written by Indexing is important and can save significant costs and improve dramatically performance.
Test Setup
Four keys scenarios were tested:
- Container with indexing policy to set to None
- Container with indexing policy to set to Automatic where all properties has been explicit excluded
- Container with indexing policy to set to Automatic where all properties except partition key has been explicit excluded
- Container with indexing policy to set to Automatic
Container may consume 400 RU/s max for each test and it store 1000 or 10000 documents in 5 or 50 partitions, distributed equally across all. The document has three properties id, orderNumber and partitionKey.
{
"id": "f72841ec-6ca5-4630-b61c-fb16f22c6336",
"orderNumber": "1",
"partitionKey": "1"
}
Test Results
The cost of all database operations is normalized by Azure Cosmos DB and is expressed by Request Units (or RUs, for short). You can think of RUs per second as the currency for throughput. RUs per second is a rate-based currency. It abstracts the system resources such as CPU, IOPS, and memory that are required to perform the database operations supported by Azure Cosmos DB.
https://docs.microsoft.com/en-us/azure/cosmos-db/request-units
| Indexing policy | Count of Documents | Count of Partitions | Write time | Write RU/s | Average Write RU/s |
| none | 1000 | 5 | 00:00:34.825 | 4950.00 | 142.14 |
| automatic (excluded all) | 1000 | 5 | 00:00:34.872 | 5520.00 | 158.29 |
| automatic (only partition key index) | 1000 | 5 | 00:00:34.980 | 5900.00 | 168.67 |
| automatic | 1000 | 5 | 00:00:34.722 | 6290.00 | 181.15 |
| Indexing policy | Count of Documents | Count of Partitions | Read time | Read RU/s | Average Read RU/s |
| none | 1000 | 5 | 00:00:01.812 | 131.30 | 72.46 |
| automatic (excluded all) | 1000 | 5 | 00:00:01.949 | 131.30 | 67.37 |
| automatic (only partition key index) | 1000 | 5 | 00:00:01.402 | 52.55 | 37.48 |
| automatic | 1000 | 5 | 00:00:01.402 | 52.55 | 37.48 |
| Indexing policy | Count of Documents | Count of Partitions | Write time | Write RU/s | Average Write RU/s |
| none | 10000 | 50 | 00:06:18.000 | 49500.00 | 130.95 |
| automatic (excluded all) | 10000 | 50 | 00:05:50.326 | 55200.00 | 157.57 |
| automatic (only partition key index) | 10000 | 50 | 00:05:39.674 | 59000.00 | 173.70 |
| automatic | 10000 | 50 | 00:05:40.000 | 62900.00 | 185.00 |
| Indexing policy | Count of Documents | Count of Partitions | Read time | Read RU/s | Average Read RU/s |
| none | 10000 | 50 | 00:00:36.612 | 11433.00 | 312.27 |
| automatic (excluded all) | 10000 | 50 | 00:00:35.164 | 11433.00 | 325.13 |
| automatic (only partition key index) | 10000 | 50 | 00:00:06.546 | 525.00 | 80.20 |
| automatic | 10000 | 50 | 00:00:03.190 | 525.00 | 164.58 |
How indexing important is can be seen by working with high load. By reading 1000 documents index hasn’t much impact on read time. But already here was visible that not indexed containers consumed as much as double of RU/s more towards indexed. But the real drama can be seen by not indexed containers when reading 10000 documents. Not indexed containers consumed as much as 21 times more RU/s as indexed, the read time increased by twelvefold.
The last one thing: you have already seen this but this is reminder. When you set indexing policy to automatic and you exclude all properties it means really everything. Not even partition key is indexed.
Source Code
https://github.com/kalcik/blog-samples/tree/master/CosmosDbPerformanceMeasurement
Anton Kalcik 