[postgis-users] Centroid out a list of points

[strk]

On Fri, Jun 12, 2009 at 11:44:24AM +0200, strk wrote:
> On Fri, Jun 12, 2009 at 10:34:21AM +0100, Pedro Doria Meunier wrote:

> > Altough the table has a gist index on the geom column the query is
> > always executed in Seq Scan mode...
> > 
> > I ask the gurus which fx is causing this, st_centroid or st_collect ?
> 
> It's the WHERE clause driving access strategy.
> If you're giving no conditions of course sequential scan is faster.
> Only reason to use the gist geometry index is if you use the &&
> operator in the WHERE clause, and the analizer finds it to be
> enough selective to prefere index vs. sequential scan.

Now, beside that, if your question is really "why is it so slow"
the answer is that collect() builds a collection of all the geometries
from the table into memory, incrementally, and only after this
the centroid() function does its work.

An improved implementation might be having an aggregate version
of the centroid() function, which might have its own drawbacks
within the GEOS interface (a conversion each row rather than a single
one for the whole collection).
All in all, for POINT-only geometries, a GEOS-free aggregate
collect() version would likely perform best.

--strk; 

 Free GIS & Flash consultant/developer      ()  ASCII Ribbon Campaign
 http://foo.keybit.net/~strk/services.html  /\  Keep it simple!