Re: what worked: performance improvements for geo-spatial searching on FreeBSD

Mark,

do you know about our sky segmentation code Q3C,
see details http://www.sai.msu.su/~megera/wiki/SkyPixelization
We use it for billions objects in database and quite happy.

Oleg

On Mon, 7 Apr 2008, Mark Stosberg wrote:

> The http://www.1-800-save-a-pet.com site is hosted with FreeBSD and
> PostgreSQL and as a geo-spatial search as a central feature.
>
> One thing that made a substantial performance improvement was switching
> from the "geo_distance()" search in the earthdistance contrib, to use
> the "cube" based geo-spatial calculations, also in available in contrib/
> In our case, the slight loss of precision between the two methods didn't
> matter.
>
> The other things that made a noticeable performance improvement was
> upgrading our servers from FreeBSD 4.x or 5.x (old, I know!) to FreeBSD
> 6.2 or later. We also upgrade these systems from PostgreSQL 8.1 to 8.2
> at the same time. I assume the upgrade to 8.2 must be responsible at
> least in part for the performance gains.
>
> The result of these two rounds of updates is that our overall CPU
> capacity in the cluster seems to be double or triple what it was before.
>
> As the site grows we continue to be very happy with the performance,
> features and stability of PostgreSQL.
>
> Mark
>
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83