7.1. Introduction

The classical example for the need of an index is if there is a table similar to this:

CREATE TABLE test1 (
    id integer,
    content varchar
);

and the application requires a lot of queries of the form

SELECT content FROM test1 WHERE id = constant;

Ordinarily, the system would have to scan the entire test1 table row by row to find all matching entries. If there are a lot of rows in test1 and only a few rows (possibly zero or one) returned by the query, then this is clearly an inefficient method. If the system were instructed to maintain an index on the id column, then it could use a more efficient method for locating matching rows. For instance, it might only have to walk a few levels deep into a search tree.

A similar approach is used in most books of non-fiction: Terms and concepts that are frequently looked up by readers are collected in an alphabetic index at the end of the book. The interested reader can scan the index relatively quickly and flip to the appropriate page, and would not have to read the entire book to find the interesting location. As it is the task of the author to anticipate the items that the readers are most likely to look up, it is the task of the database programmer to foresee which indexes would be of advantage.

The following command would be used to create the index on the id column, as discussed:

CREATE INDEX test1_id_index ON test1 (id);

The name test1_id_index can be chosen freely, but you should pick something that enables you to remember later what the index was for.

To remove an index, use the DROP INDEX command. Indices can be added and removed from tables at any time.

Once the index is created, no further intervention is required: the system will use the index when it thinks it would be more efficient than a sequential table scan. But you may have to run the VACUUM ANALYZE command regularly to update statistics to allow the query planner to make educated decisions. Also read Chapter 11 for information about how to find out whether an index is used and when and why the planner may choose to not use an index.

Indices can also benefit UPDATEs and DELETEs with search conditions. Note that a query or data manipulation commands can only use at most one index per table. Indices can also be used in table join methods. Thus, an index defined on a column that is part of a join condition can significantly speed up queries with joins.

When an index is created, it has to be kept synchronized with the table. This adds overhead to data manipulation operations. Therefore indices that are non-essential or do not get used at all should be removed.