Solving common problems

After this brief introduction to indexes, the focus of this chapter will shift to troubleshooting. The most common challenges related to indexing and troubleshooting will be described in this section.

Managing foreign keys

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.

Note

Keep in mind that PostgreSQL does not create an automatic index...