Indexes

An index is a physical database object that is defined on a table column or a list of columns. In PostgreSQL, there are many types of indexes and several ways to use them. Indexes can be used, in general, to do the following:

• Optimize performance: an index allows an efficient retrieval of a small number of rows from the table. The small number is often determined by the total number of rows in the table and execution planner settings.
• Validate constraints, instead of checking constraints. An index can be used to validate the constraints on several rows. For example, the UNIQUE check constraint creates a unique index on the column behind the scenes.

Let us take a look at the account_history table in the car web portal example. The unique constraint, UNIQUE (account_id, search_key, search_date), has two purposes: the first purpose is to define the validity constraint for inserting the search key into the table only once each day, even if the user searches for the key several times. The...