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 = constant;
With no advance preparation, the system would have to scan entire 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.