Indexing Engine CP Model: Custom Configuration - Goals

Goals are the main components that guide the model toward the solution that best meets the requirements of the user. These requirements can be defined using any combination of goals.

Its important to note that the order of the goals always matters. Consider the case of minimizing the costs (Minimize Total Cost) and minimizing the number of indexes (Minimize Number of Indexes).

On the one hand, Minimize Total Cost will tend toward an index suggestion that uses many indexes, since this naturally allows small costs for the scans. On the other hand, Minimize Number of Indexes will tend toward an index suggestion that obviously uses few indexes. These two goals are conflicting, and it is ambiguous to determine, without additional user input, if low costs are preferrable to few indexes, or vice-versa. This is why goal ordering is necessary.

Further, some goals should not be selected as the first goals to optimize.

For example, suppose that the first goal is Minimize Number of Indexes. The model is essentially told that "use the fewest indexes" is the most important goal. Obviously, 0 indexes is the fewest indexes it can use. Subsequent goals will not be able to make use of any indexes because of this.

Goals that should be avoided as the first goal are Minimize Number of Indexes, Minimize Index Write Overhead, and Minimize Update Overhead, unless there is a rule that is focused on the scan cost, like Maximum Per-Scan Cost Tolerance.

Minimize Index Write Overhead

The Minimize Index Write Overhead goal strives to minimize the index write overhead associated with the selected indexes.

{ "Name": "Minimize Index Write Overhead", "Tolerance": 0.0 }

Minimize Number of Indexes

The Minimize Number of Indexes goal strives to minimize the number of existing and possible indexes selected.

{ "Name": "Minimize Number of Indexes", "Tolerance": 0.0 }

Minimize Total Cost

The Minimize Total Cost goal strives to minimize the combined costs of the scans.

{ "Name": "Minimize Total Cost", "Tolerance": 0.1 }

Minimize Maximum Cost

The Minimize Maximum Cost goal strives to minimize the largest cost found among the scans.

{ "Name": "Minimize Maximum Cost", "Tolerance": 1.0 }

Minimize Maximum Relative Cost

The Minimize Maximum Relative Cost goal strives to minimize the largest relative cost found among the scans. The relative cost of a scan is equal to its actual value divided by the best possible value it could get in theory.

{ "Name": "Minimize Maximum Relative Cost", "Tolerance": 10.0 }

Minimize Total Weighted Cost

The Minimize Total Weighted Cost goal strives to minimize the combined weighted costs of the scans.

The weighted cost of a scan is a measure of its influence on performance, and is equal to its cost multiplied by the frequency in which it appears in queries. Common scans with high costs will tend to have a higher weighted cost than uncommon scans with lower costs.

{ "Name": "Minimize Total Weighted Cost", "Tolerance": 0.1 }

Minimize Maximum Weighted Cost

The Minimize Maximum Weighted Cost goal strives to minimize the largest weighted cost found among the scans.

{ "Name": "Minimize Maximum Weighted Cost", "Tolerance": 1.0 }

Minimize Maximum Relative Weighted Cost

The Minimize Maximum Relative Weighted Cost goal strives to minimize the largest relative weighted cost found among the scans. The relative weighted cost of a scan is equal to its actual value divided by the best possible value it could get in theory.

{ "Name": "Minimize Maximum Relative Weighted Cost", "Tolerance": 10.0 }

Minimize Update Overhead

The Minimize Update Overhead goal strives to minimize the update overhead of the selected indexes.

{ "Name": "Minimize Update Overhead", "Tolerance": 0 }