Indexing the partition key in Azure Cosmos DB

Question

Suppose I've the following data in my container:

{
    "id": "1DBF704E-1623-4844-DC86-EFA729A5C048",
    "firstName": "Wylie",
    "lastName": "Ramsey",
    "country": "AZ",
    "city": "Tucson"
}

Where I use the field "id" as the item id, and the field "country" as the partition key, when I query on specific partition key:

SELECT * FROM c WHERE c.country = "AZ"

(get all the people in "AZ")

Should I add "country" as an index or I will get it by default, since I declered "country" as my partition key? Is there a diference when using the SDK (meaning: adding the new PartitionKey("AZ") option and then sending the query as mentioned above)?

By default, Azure Cosmos DB automatically indexes every property for all items in your container without having to define any schema or configure secondary indexes. See [this](https://learn.microsoft.com/en-us/azure/cosmos-db/index-overview) — Tiny Wang, May 09 '21 at 14:12
And on the query statement, you can refer to azure official sample, and you'll find it really no need. [C# sapmle](https://github.com/Azure-Samples/cosmos-dotnet-getting-started/blob/main/CosmosGettingStartedTutorial/Program.cs#L207), [nodejs sample](https://github.com/Azure-Samples/azure-cosmos-db-sql-api-nodejs-getting-started/blob/main/app.js#L37) — Tiny Wang, May 09 '21 at 14:18
don't worry about the index, but if you wanna custom index policy, refer to [this doc](https://learn.microsoft.com/en-us/azure/cosmos-db/index-policy) may help. — Tiny Wang, May 09 '21 at 14:21
The Indexing policies doc doesn't mention anything about indexing the partition key — galbru, May 09 '21 at 14:24
Using partition key as the filtering key is a good practice. [If most of your workload's requests are queries and most of your queries have an equality filter on the same property, this property can be a good partition key choice](https://learn.microsoft.com/en-us/azure/cosmos-db/partitioning-overview#partition-keys-for-read-heavy-containers). — Tiny Wang, May 09 '21 at 14:28
If you'd like to set custom index policy, you should set index according to your querying requirement, index is used to improve query performance, right? So I think it's necessary to set index on partition key in your scenario :) — Tiny Wang, May 09 '21 at 14:37
Not sure why this question is downvoted, it's a perfectly valid question. Also, for the replies above, I suppose the question is not whether partition key should have an index or not, it should definitely have an index. The question here is whether, it should be explicitly declared in the indexing policy json or it would automatically exist, even if `/*` is added to the `excludedPaths`. Correct me, if I am wrong @galbru — mickeymoon, Jul 28 '21 at 09:05

score 17 · Answer 1 · answered Aug 05 '21 at 22:23

I created a collection with 50,000 records and disabled indexing on all properties.

Indexing policy:

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [], // Included nothing
    "excludedPaths": [
        {
            "path": "/\"_etag\"/?"
        },
        {
            "path": "/*" // Exclude all paths
        }
    ]
}

Querying by id cost 2.85 RUs. Querying by PartitionKey cost 580 RUs.

Indexing policy with PartitionKey (country) added:

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/country/*"
        }
    ],
    "excludedPaths": [
        {
            "path": "/\"_etag\"/?"
        },
        {
            "path": "/*" // Exclude all paths
        }
    ]
}

Adding an index on the PartitionKey brought it down to 2.83 RUs.

So the answer to this is Yes, if you have disabled default indexing policies and need to search by partition key then you should add an index to it.

Documentation is not super clear about it but seems to recommend indexing the PK https://learn.microsoft.com/en-us/azure/cosmos-db/index-policy#includeexclude-strategy: "Exclude the root path to selectively include paths that need to be indexed. The partition key property path isn't indexed by default with the exclude strategy and should be explicitly included if needed." — Fabian Nicollier, Apr 19 '23 at 12:27

score -1 · Answer 2 · edited Jul 28 '21 at 08:59

-1

In my opinion, it's a good practice to query with partition key in cosmosdb sql api, here's the offical doc related to it.

By the way, cosmosdb sql api indexes all the properties by default. If you'd like to cover the default setting and customise the indexing policy, this doc may provide more details.

edited Jul 28 '21 at 08:59

mickeymoon

4,820
5
31
56

answered May 09 '21 at 14:44

Tiny Wang

10,423
1
11
29

Indexing the partition key in Azure Cosmos DB

2 Answers2