1.1 The Power of Principles

Tuning rests on a foundation of informed common sense. This makes it both easy and hard.

Tuning is easy because the tuner need not struggle through complicated formulas or theorems. Many academic and industrial researchers have tried to put tuning and query processing generally on a mathematical basis. The more complicated of these efforts have generally foundered because they rest on unrealizable assumptions. The simpler of these efforts offer useful qualitative and simple quantitative insights that we will exploit in the coming chapters.

Tuning is difficult because the principles and knowledge underlying that common sense require a broad and deep understanding of the application, the database software, the operating system, and the physics of the hardware. Most tuning books offer practical rules of thumb but don't mention their limitations.

For example, a book may tell you never to use aggregate functions (such as AVG) when transaction response time is critical. The underlying reason is that such functions must scan substantial amounts of data and therefore may block other queries. So the rule is generally true, but it may not hold if the average applies to a few tuples that have been selected by an index. The point of the example is that the tuner must understand the reason for the rule, namely, long transactions that access large portions of shared data may delay concurrent online transactions. The well-informed tuner will then take this rule for what it is: an example of a principle (don't...