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.

It is highly recommended to ensure that both sides of the equation are indexed.

Indexing geometric data leads many people into a simple trap—they try to store geometric objects in a traditional way. I have already pointed out this trap in one of the previous chapters.

But how can things be done properly now? The PostGIS project (http://postgis.net/) has all that it takes to properly index geometric data. PostGIS is built on the so-called GiST index, which is a part of PostgreSQL. What is a GiST? The idea of GiST is to offer an indexing structure that provides alternative algorithms that a normal B-tree is not capable of providing (for example, operations such as contains).

The technical details outlining how GiST works internally would definitely be way beyond the scope of this book. Therefore I advise you to check out http://postgis.net/docs/manual-2.1/using_postgis_dbmanagement.html#idp7246368 for further information on GiST and indexing geometric data.