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.
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.