Suppose we have a table similar to this:
CREATE TABLE test1 ( id integer, content varchar );
and the application issues many queries of the form:
SELECT content FROM test1 WHERE id =
With no advance preparation, the system would have to scan the
test1 table, row by row,
to find all matching entries. If there are many rows in
test1 and only a few rows
(perhaps zero or one) that would be returned by such a query,
this is clearly an inefficient method. But if the system has been
instructed to maintain an index on the
id column, it can 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 non-fiction books: 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(s), rather than having to read the entire book to find the material of interest. Just as it is the task of the author to anticipate the items that readers are likely to look up, it is the task of the database programmer to foresee which indexes will be useful.
The following command can be used to create an index on the
id column, as discussed:
CREATE INDEX test1_id_index ON test1 (id);
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
INDEX command. Indexes can be added to and removed from
tables at any time.
Once an index is created, no further intervention is required:
the system will update the index when the table is modified, and
it will use the index in queries when it thinks doing so would be
more efficient than a sequential table scan. But you might have
to run the
ANALYZE command regularly
to update statistics to allow the query planner to make educated
decisions. See Chapter 14
for information about how to find out whether an index is used
and when and why the planner might choose not to use an index.
Indexes can also benefit
DELETE commands with search
conditions. Indexes can moreover be used in join searches. Thus,
an index defined on a column that is part of a join condition can
also significantly speed up queries with joins.
Creating an index on a large table can take a long time. By
default, PostgreSQL allows reads
SELECT statements) to occur on the
table in parallel with index creation, but writes (
DELETE) are blocked until the index
build is finished. In production environments this is often
unacceptable. It is possible to allow writes to occur in parallel
with index creation, but there are several caveats to be aware of
— for more information see Building Indexes
After an index is created, the system has to keep it synchronized with the table. This adds overhead to data manipulation operations. Therefore indexes that are seldom or never used in queries should be removed.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.