Given the munros relation:
CREATE TABLE munros(
mid int NOT NULL,
mname varchar(80) NOT NULL,
gridref char(10) NOT NULL,
heightm int NOT NULL,
Suppose that your application requires a lot of queries of the form:
SELECT * FROM munros WHERE gridref = constant
With no advance preparation, the system would have to scan the entire munros relation,
row by row, to find all Munros in the specific loaction. However, only a few Munros will match a
particular location (you should be able to write a query that returns the maximum number of Munros
in any of the grid locations). If there are only a few Munros returned by the query (in our example
it will only be zero or one), then scanning the entire relation is clearly an inefficient method
(especially for large tables, e.g., assume a relation containing all the mountains
in the world). If the system has been instructed to maintain an index on the gridref column,
then it can use a more efficient method for locating matching Munros. 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 gridref column, as discussed (check
the full syntax of CREATE INDEX in the on-line documentation):
CREATE INDEX idx_munros_gridref ON munros(gridref);
The name of an index can be chosen freely, e.g., in this case it is idx_munros_gridref. The
indexes that are defined on a relation are listed by the psql command \d relation:
sXXXXXX=> \d munros
Column | Type | Modifiers
mid | integer | not null default nextval('seq_munros_id'::reg class)
mname | character varying(80) | not null
gridref | character(10) | not null
heightm | integer | not null
"munros_pkey" PRIMARY KEY, btree (mid)
"idx_munros_gridref" btree (gridref)
Many of you have noticed already that the system automatically creates an index for
the primary key of a relation. Since the primary key is unique it does make sense to
use such an index when searching for rows with a particular primary key value.
To remove an index, use the DROP INDEX command.
Indexes can be added to and removed from tables at any time.
DROP INDEX idx_munros_gridref;
Question: What happens when you try to drop the index on the primary key?
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.