What PostGIS Is Actually Doing When Your Spatial Query Is Slow

The first decision you make in PostGIS is the column type. Most people make it without noticing they made one.

Geometry or geography

Geometry is planar. It treats your coordinates as points on a flat Cartesian sheet, which keeps the math cheap and fast. Geography models the Earth as a spheroid. On lat/lon data it gives you distances in meters that are correct by default, but every calculation costs a lot more.

You can get this wrong in either direction. If you store lat/lon (SRID 4326) in a geometry column and call ST_Distance or ST_DWithin, the answer comes back in degrees. A degree is not a fixed distance. A degree of longitude is roughly 111 km at the equator and shrinks toward zero at the poles, so a filter like "within 0.01" covers a different real-world area depending on where your data sits. Nothing errors. The results are just quietly wrong.

The opposite mistake is picking geography because it works out of the box with GPS coordinates and then wondering why everything is slow. Spheroidal math is far heavier than planar math. The gap grows with complex polygons that have many vertices. For data that lives in one city or one country I'd use geometry in a suitable projected coordinate system, such as a local UTM zone or a national grid. Units there are meters and the flat-Earth assumption holds well enough. Geography earns its cost when the data really is global.

What the GiST index holds

Testing whether two polygons intersect means walking their edges. You don't want to do that for every row in a table. A GiST index is how you avoid most of it. It holds each geometry's bounding box in an R-tree-like structure rather than the shape itself. The one question it answers cheaply is which boxes overlap a given box. In SQL that question is the && operator. Most of the real work happens there.

Index-aware functions such as ST_Intersects and ST_Contains run in two stages. So does ST_DWithin. First the index throws away every row whose bounding box can't possibly match. Then the exact geometry test runs only on what's left.

Long diagonal or sprawling shapes suffer under this scheme. Their boxes are mostly empty space, so they pass the cheap filter and land in the expensive step far more often than they should.

Without a GiST index there is no first stage. PostGIS does a sequential scan and runs the full geometry math on every single row. That's fine at ten thousand rows and miserable at ten million.

ST_Distance in a WHERE clause

The most common PostGIS performance footgun I know of looks like this (assume a projected SRID in meters, per the geometry section above):

WHERE ST_Distance(a.geom, b.geom) < 500

It reads naturally and it returns the right rows. It also can't use the spatial index. The planner sees a function returning a number compared against a constant instead of a spatial predicate, so it computes an exact distance for every row and then compares.

This returns the same rows and was built for the job:

WHERE ST_DWithin(a.geom, b.geom, 500)

Internally ST_DWithin expands the search geometry's bounding box by the distance and adds an && check that the GiST index can serve. The exact distance test only runs on the candidates that come through. Same answer, often orders of magnitude less work on a large table. I keep ST_Distance in the SELECT list for when I actually need the number.

Read the plan

Run EXPLAIN ANALYZE and look at what comes back. An Index Scan or Bitmap Index Scan on your GiST index means the pre-filter is doing its job. A Seq Scan with your spatial function sitting in the Filter line means it isn't, however sure you are that there's an index on that column.

The cause might be the query shape. It might be a forgotten ANALYZE that left the planner with bad statistics. On a small table a sequential scan is sometimes just the cheaper plan. You find out which one from the plan, not from intuition.

I wrote about how a database like this fits underneath an LLM-driven retrieval system in Using the LLM as a Query Planner. This post is the layer below that one, covering what happens once the predicate actually lands in PostGIS.