CODEWITHSUNDEEP
azure-cosmosdbdatabase-partitioningazure-cosmosdb-sqlapi
galbru
galbru

Reputation: 452

Indexing the partition key in Azure Cosmos DB

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 declared "country" as my partition key? Is there a difference when using the SDK (meaning: adding the new PartitionKey("AZ") option and then sending the query as mentioned above)?

Upvotes: 11

Views: 4225

Answers (2)

lahsrah
lahsrah

Reputation: 9173

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.

Upvotes: 19

Tiny Wang
Tiny Wang

Reputation: 15906

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.

Upvotes: -1

Related Questions