Managing Indexes

An index is necessary on any column or combination of columns that must be unique. However, as discussed in Queries and the Query Optimizer,, the presence of an index can also allow the query optimizer to speed up a query. The optimizer can use an index in the following ways:

• To replace repeated sequential scans of a table with nonsequential access
• To avoid reading row data when processing expressions that name only indexed columns
• To avoid a sort (including building a temporary table) when executing the GROUP BY and ORDER BY clauses

As a result, an index on the appropriate column can save thousands, tens of thousands, or in extreme cases, even millions of disk operations during a query. However, indexes entail costs.