Text Size: Normal / Large

Chapter 11. Indexes


11.1. Introduction

Suppose we have a table similar to this:

    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.



CREATE INDEX test1_id_index ON test1 (id);



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 Chapter 13 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 UPDATE and DELETE commands with search conditions. Indexes can moreover be used in join queries. Thus, an index defined on a column that is part of a join condition can significantly speed up queries with joins.


Privacy Policy | About PostgreSQL
Copyright © 1996-2013 The PostgreSQL Global Development Group