After creation, the container appointments has an indexing policy that indexes every property. That is convenient and costs on every write. But Kladde.Verlauf asks only a few things: the calendar of a month, the history of a customer, later the appointments of an office by month and start. This lesson shows how to tailor the policy to that and how to use the SDK's metrics to check whether a query really uses the index.

Not run against Azure. The code was compiled with Microsoft.Azure.Cosmos 3.64.0 and Newtonsoft.Json 13.0.4. Only the check of the policy and the reading of the index metrics from text were run (Exercise 1). No index rebuild, no query with metrics and no RU measurement took place against an account; what is said about the service comes from Microsoft Learn, read on the day of writing.

The indexing policy

The policy is a JSON document on the container. It determines which paths are indexed and how. Learn knows four kinds of index: range index (equality, ranges, ORDER BY, JOIN, many string functions), spatial, composite and vector index (lesson 4.1). The default policy indexes every property of every item with range indexes for numbers and strings.

  • Mode. consistent: The index is kept up to date synchronously on write. none: no indexing, for example for a pure key-value store. lazy cannot be chosen for new containers and can return incomplete results.
  • Paths. A path to a value ends in /? (/title/?), an array element is written /[], everything below it /*. Every policy needs the root path /* either among the included or among the excluded paths. Learn recommends including the root and excluding paths you do not need; that way a property added later is indexed too. In case of conflicts, the more specific path wins.
  • Always included. id and _ts are always indexed in mode consistent; _etag is excluded by default.
  • Key paths. The levels of the partition key belong in the index (/officeId, /month). With the root /* included, they are; if you exclude the root, you must include them explicitly, otherwise queries on the key hierarchy read the whole data set and cost more RU.
  • Cost. Every indexed path costs RU and storage on write. The index can be larger than the data. Excluded paths reduce both.

Free texts that nobody filters on should be excluded. In the Kladde, that is notes. The appointments also need two composite indexes, see below.