One of the most important issues causing trouble and confusion when it comes to indexing has to do with foreign keys. Consider the following example:

test=# CREATE TABLE t_person (id   int   PRIMARY KEY, 
          name text);
CREATE TABLE
test=# CREATE TABLE t_car (car_id   int, 
    person_id   int REFERENCES t_person (id),
     info     text);
CREATE TABLE

It is necessary to have a unique index irrespective of a primary key on the t_person table. However, we often forget to index the other side of the foreign key (in our case, the t_car table). Just imagine looking up all cars owned by a certain person. You would definitely want an index scan on the t_car table to speed up this query.