[postgis-users] Over-complicated shapes slowing down my queries?

[Sandro Santilli]

On Fri, Mar 16, 2012 at 05:38:07PM -0400, Dan Bernier wrote:
> Hello, and apologies in advance if this is a naive question - I'm
> still very new to GIS.
> 
> Does PostGIS provide a way to measure the "complexity" of a shape?
> 
> I have some shapes that I suspect are slowing down queries - normally,
> joining against them with ST_within takes < 1 second, but for some
> rows, it takes from 3 to 25 seconds. The explain analyze for the
> different queries looks the same. The complexity of the shape is the
> only thing I can think of that might cause this, but I don't know of
> any way to measure this, to guide my reasoning. I tried googling, but
> don't quite know the right terms to search for.

The complexity you can measure by counting number of vertices:
SELECT gid, st_npoints(the_geom) from mytable;

> Beyond general shape-complexity, are there any other properties of a
> shape that might cause this kind of slow-down?

Your queries make use of GEOS calls (ST_Within).
Such call may apply an heuristic to catch robustness issues.
If it does it will perform the operation again after reducing precision.
Then if it fails, it'll do again with another approach.
And so on.

See if passing the "expensive" inputs trought ST_SnapToGrid helps.

--strk;

  ,------o-. 
  |   __/  |    Delivering high quality PostGIS 2.0 !
  |  / 2.0 |    http://strk.keybit.net - http://vizzuality.com
  `-o------'