|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
Suppose we have 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;
With no advance preparation, 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 (perhaps only zero
or one) that would be returned by such a query, then this is clearly an
inefficient method. But if the system has been instructed to maintain an
index on the
id column, then 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 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(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 the readers are likely to look up, it is the task of the database programmer to foresee which indexes will 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);
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.
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 this would be more efficient
than a sequential table scan. But you may have to run the
ANALYZE command regularly to update
statistics to allow the query planner to make educated decisions.
See section 11 Performance Tips for information about
how to find out whether an index is used and when and why the
planner may 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
significantly speed up queries with joins.
Creating an index on a large table can take a long time. By default, PostgreSQL allows reads (selects) to occur on the table in parallel with creation of an index, but writes (inserts, updates, deletes) 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 Concurrently.
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.
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|