How do I use spatial indexes?

Spatial indexes are hugely important for getting good performance on spatial SQL queries! You need to do two things to use a spatial index:

• Create the spatial index, and;
• Use a function that is “spatial index aware”.

To create a spatial index, use the CREATE INDEX command, but specify the USING GIST access method, like so:

CREATE INDEX mytable_geom_x ON mytable USING GIST (geom)

When bound to the GIST access method, a the default index for a geometry is an R-Tree. If you forget to include the USING GIST part, you will get a standard PostgreSQL “B-Tree” index, which is a non-spatial index and will not help your queries.

There are a limited set of functions that can make use of a spatial index as a pre-filter, to speed up queries, and here they are:

• ST_LineCrossingDirection
• ST_DWithin
• ST_Touches
• ST_Intersects
• ST_Crosses
• ST_Contains
• ST_ContainsProperly
• ST_Within
• ST_Covers
• ST_CoveredBy
• ST_Overlaps
• ST_DFullyWithin
• ST_3DDWithin
• ST_3DDFullyWithin
• ST_3DIntersects
• ST_OrderingEquals
• ST_Equals

So a spatial join query can make use of a spatial relationship function and get index acceleration like this:

SELECT a.*
FROM a
WHERE ST_Intersects(a.geom, ST_Point(-126, 45, 4326))

And can use a spatial relationship to drive a join, like this:

SELECT a.name, b.id
FROM a
JOIN b
  ON ST_Contains(a.geom, b.geom)
WHERE a.name = 'Pleasantown'