Database Reference
In-Depth Information
Using indexes
A database index is very much like the index of a book (such as this one). While a
book'sindexindicatesthepagesonwhichawordispresent,adatabasecolumnin-
dexindicatestherowsinatablecontainingasearchedvalue.Justasabook'sindex
does not indicate exactly where on the page a word is located, the database index
maynotbeabletodenotetheexactlocationofthesearchedvalueinarow'scolumn.
PostgreSQLhasseveraltypesofindexes,suchas
B-Tree
,
Hash
,
GIST
,
SP-GIST
,
and
GIN
.Alltheseindextypesaredesignedtohelpqueriesfindmatchingrowsfaster.
What makes the indexes different is the underlying algorithms. Generally, to keep
thingssimple,almostallPostgreSQLindexesareofthe
B-Tree
type.PostGIS(spa-
tial) indexes are of the
GIST
type.
Geometries, geographies, andrastersarealllarge, complexobjects, andrelating to
or among these objects takes time. Spatial indexes are added to the PostGIS data
typestoimprovesearchperformance.Theperformanceimprovementcomesnotfrom
comparingpotentiallycomplex,actualspatialobjects,butratherthesimplebounding
boxes of those objects.
Getting ready
For this recipe,
psql
will be used as follows to time the queries:
> psql -d chapter10
chapter10=# \timing on
We will use the
caschools
and
sfpoly
tables loaded in this chapter's first recipe.
How to do it...
Thebestwaytoseehowaquerycanbeaffectedbyanindexisbyrunningthequery
beforeandaftertheadditionofanindex.Thefollowingstepswillguideyouthrough
the process of optimizing a query with an index:
