Cosmos db sql; partition key on feed options vs querying on the partition key?

Question

What is the difference between giving partition key in the .net core sdk FeedOptions vs having it as a criteria on the query? For example if you want to list all items within a given partition key. Should you go with feed options or partition key in the criteria?

Partition key in criteria

SELECT * FROM c where c.PartitionKey = "some partition key"

.net core with feedoptions

var allDocs = await (from d in client.CreateDocumentQuery<Document>(UriFactory.CreateDocumentCollectionUri(databaseId, "TestCollection"), 
                     new FeedOptions { PartitionKey = new PartitionKey("some partition key") })
                     select d)
                    .AsDocumentQuery<Document>().ExecuteNextAsync<Document>();

Question came up when I created a custom indexing policy where partition key is excluded from indexing. Hence, while looking the the RU/s cost it appears to do a scan when excluded instead of an index lookup while using the default indexing policy.

Does that mean that if you need cross partition queries you need to index the partition key, and if you only need to query within given partition keys you can exclude it - assuming you give partition key in FeedOptions?

Answer 1

I am from the CosmosDB engineering team.

There are two questions asked from your post, which are orthogonal to each other:

1. Do we specify PartitionKey in the feedOptions or in the query?

This shouldn't matter. Specifying it in either the FeedOptions or the query itself will help it route to the correct partition for execution.

2. Do we need to add partition key to the indexing policy for efficient query execution?

You should always add the partition key to the indexing policy. This ensures that during query execution, the index is utilized to select the documents for the partition key(s) specified in the query.