Practical PostgreSQL

Indices

Indices are database objects that can greatly increase database performance, enabling faster execution of statements involving comparative criteria. An index tracks the data on one or more columns in a table, allowing conditional clauses (such as the WHERE clause) to find their targeted rows more efficiently.

The internal workings of indices vary, and there are several implementations to choose from. This section describes the different types of indices available, and explains when you should use one type over the other.

While indices exist to enhance performance, they also contribute to system overhead. Indices must be updated as data in the column that they are applied to fluctuates. Maintaining infrequently used indices decreases performance when the amount of time spent maintaining them outweighs the time saved through using them. In general, indices should be applied only to columns that you expect to use frequently in comparative expressions.

Creating an Index

Indices are created with the CREATE INDEX SQL command. Here is the syntax for CREATE INDEX:

CREATE [ UNIQUE ] INDEX indexname ON table
       [ USING indextype ] ( column [ opclass ] [, ...] )

In this syntax, indexname is the name of the new index to be created, table is the name of the table to be indexed, and column is the name of a specific column to be indexed. Optionally, the indextype parameter may be set to specify index implementation, and the opclass parameter may be set to indicate what operator class should be ...