Indexing Engine CP Model: Custom Configuration

The model takes as input a settings JSON object. All settings are optional, and will be assigned their default value if omitted. The settings must follow this structure:

  "Method": "CP",
  "Options": {
    "Goals": [
        "Name": "Minimize Total Cost",
        "Tolerance": 0.1
        "Name": "Minimize Index Write Overhead",
        "Tolerance": 0.0
    "Rules": [
        "Name": "Maximum Number of Indexes",
        "Value": 10

The two main fields are Method and Options. Method defines the method used for performing index selection, and can be one of CP or Greedy. The current default is Greedy for backwards compatibility purposes. The Options can customize the behavior of the chosen method.

CP Method

Roughly, the CP method works as follows:

  1. Validate the data and the settings
  2. Create a basic model
  3. Add all the rules to the model
  4. For each goal:
    • Optimize the goal
    • Transform the goal into a rule (taking tolerance into account)
  5. Return the selection of indexes

It has multiple options to allow customization:

Goalsarray[Goal]n/an/a[]Ordered list of goals
Rulesarray[Rule]n/an/a[]List of rules
Time Limitfloat0.0Time allotted to the model

Each goal is defined by its name and tolerance (0.0 to ∞). The available goals are:

The rules are defined by their name and an associated value. If a rule is not defined in the settings, it will be automatically created and it will be assigned its default value:

Rule NameTypeMinMaxDefaultDescription
Maximum Per-Scan Cost Tolerancefloat0.0Scan cost tolerance w.r.t. best cost
Maximum Per-Scan Weighted Cost Tolerancefloat0.0Scan weighted cost tolerance w.r.t. best weighted cost
Maximum Number of Indexesinteger0Maximum number of indexes suggested
Maximum Index Write Overheadfloat0.0Maximum index write overhead allowed
Minimum Coveragefloat0.01.00.0Portion of coverable scans covered


The tolerance parameter is a measure of how strict the ordering of the goals is. It allows some amount of flexibility in the ordering of the goals. Each goal has an associated tolerance value in the range [0.0, ∞] (defaulted to 0). When a goal is optimized, the resulting value indicates how well that goal has met its stated objective. The tolerance of that goal, in turn, indicates how close subsequent goals should stick to the value found for the original goal.


There are two goals:

  1. Minimize Total Cost (with a tolerance of 0.1)
  2. Minimize Index Write Overhead

Suppose that the total cost found by the first goal is 200. Normally (with a tolerance of 0) the second goal should determine the lowest index write overhead required to achieve costs no higher than 200.

However, with a tolerance of 0.1, the second goal will instead determine the lowest index write overhead required to achieve costs no higher than 220 (because 200 + 10% = 220).

Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →