Using indexes

A database index is very much like the index of a book (such as this one). While a book's index indicates the pages on which a word is present, a database column index indicates the rows in a table containing a searched value. Just as a book's index does not indicate exactly where on the page a word is located, the database index may not be able to denote the exact location of the searched value in a row's column.

PostgreSQL has several types of indexes, such as B-Tree, Hash, GIST, SP-GIST, and GIN. All these index types are designed to help queries find matching rows faster. What makes the indexes different is the underlying algorithms. Generally, to keep things simple, almost all PostgreSQL indexes are of the B-Tree type. PostGIS (spatial) indexes are of the GIST type.

Geometries, geographies, and rasters are all large, complex objects, and relating to or among these objects takes time. Spatial indexes are added to the PostGIS data types to improve search performance. The performance...