If I have 'id' field specified as the partition key, does this mean that any indexes on other fields are useless? Because if I search by some other field, say 'name', for which I have an index, that would still mean that db engine would need to go through all index instances for each logical partition.
Or do logical partitions within a physical partition share a single index instance. In that case even if have partition key set to 'id', indexes on other fields will increase performance of read queries?
Example:
Document structure:
{
id: string
name: string
}
Partition Key = "/id"
Query:
SELECT * from c
WHERE c.name = "John"
Will that query use index on field 'name'? (assuming indexing policy is in it's default state with automatic indexing of all fields)
More specifically, will it be a single index lookup assuming all logical partitions are within a single physical partition? Or will it need to make n index lookups where n is number of logical partitions?
If you have a million logical partitions in a single physical partition CosmosDB certainly doesn't create 1 million index structures and fan your query out on non partition key properties across those.
You get a single inverted index per physical partition.
(Or in the case of a shared throughput database a single inverted index per collection per physical partition.)
Note "indexes on other fields" don't create separate indexes even in APIs like Mongo API which give the impression of this.
You just control the paths that are contained in the inverted index.