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 that contain a searched-for 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-for value in a row's column.
PostgreSQL has several types of index, such as B-Tree, Hash, GIST, SP-GIST, and GIN. All of these index types are designed to help queries find matching rows faster. What makes the indices different are the underlying algorithms. Generally, to keep things simple, almost all PostgreSQL indexes are of the B-Tree type. PostGIS (spatial) indices are of the GIST type.
Geometries, geographies, and rasters are all large, complex objects, and relating to or among these objects takes time. Spatial indices are added to the PostGIS data types to improve search performance...
