Reactive Index Design - Relational Database Index Design and the Optimizers - page 105

Postponing most of the index tuning for an application until after production
cutover is indeed like pushing a large jet full of passengers off a cliff. It may have
a happy ending, but the database crew must be prepared to take quick action. If
nobody has paid much attention to indexing during the application development,
some programs are likely to be painfully slow after production cutover.
Of course, we do not recommend the Wright brothers' approach. We believe
the indexes for an application should be well tuned before cutover. However, even
if quick estimates and a quick EXPLAIN review are done for all programs, some
performance surprises will undoubtedly arise when production starts. Estimates
are never perfect; it is easy to miss something when one estimates the worst input
filter factors, for instance.
In this chapter we will discuss the performance tools and techniques that
are essential during the first days of the life of an application in production.
These same tools and techniques are also useful later for more relaxed tuning,
to find performance problems before the users are aware of them, or at least
before they become too painful; in this case we are actually being proactive
rather than reactive.
EXPLAIN DESCRIBES THE SELECTED ACCESS PATHS
It is quite easy to identify suspicious access paths, particularly if the EXPLAIN
output is stored in a table, thereby enabling easy accessibility. This is why
the analysis of SQL statements, for which the optimizer has selected a suspi-
cious access path, often starts the index improvement process. The following
performance alarms that we have studied in some depth are quickly detected
with EXPLAIN.
Full Table Scan or Full Index Scan
If the most suspicious access paths, scanning the whole index or the whole table,
have not already been checked before cutover, now is the time to do it.
Sorting Result Rows
A sort of the result rows is the most useful alarm after that for full scans. There
may be two reasons for the sort:
1. There is not an index that would make the sort unnecessary for the
SELECT statement.
2. The access path chosen by the optimizer includes a redundant sort.
In the first case, the sort can be eliminated by an improvement of the index (refer
to Chapters 4 and 5). The second case will be discussed in Chapter 14.
Often a sort does no harm. For example, an application may have a thousand
different SELECT statements with hundreds of them having a sort in their access