Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Back to homepage

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:

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

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

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

  ON ST_Contains(a.geom, b.geom)
WHERE = 'Pleasantown'