pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwhat worked: performance improvements for geo-spatial searching on FreeBSD
4+ messages / 2 participants
[nested] [flat]
* what worked: performance improvements for geo-spatial searching on FreeBSD
@ 2008-04-07 18:35 Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 4+ messages in thread
From: Mark Stosberg @ 2008-04-07 18:35 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: what worked: performance improvements for geo-spatial searching on FreeBSD
@ 2008-04-07 18:47 Oleg Bartunov <oleg@sai.msu.su>
parent: Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 4+ messages in thread
From: Oleg Bartunov @ 2008-04-07 18:47 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: what worked: performance improvements for geo-spatial searching on FreeBSD
@ 2008-04-08 13:45 Mark Stosberg <mark@summersault.com>
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 4+ messages in thread
From: Mark Stosberg @ 2008-04-08 13:45 UTC (permalink / raw)
To: pgsql-performance
Oleg Bartunov wrote:
> 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,
Thanks for the response. That sounds interesting, but it's not clear to
me how I would put together a geo-spatial search calculating distances
around the curvature of the earth using this technique. Is there is a
SQL sample for this that you could point to?
Also, I didn't recognize the names of the techniques you were
benchmarking against "RADEC" and "Rtree", are either of these related to
the "earthdistance" or "cube()" based searches I would have used already?
Mark
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Re: what worked: performance improvements for geo-spatial searching on FreeBSD
@ 2008-04-08 17:38 Oleg Bartunov <oleg@sai.msu.su>
parent: Mark Stosberg <mark@summersault.com>
0 siblings, 0 replies; 4+ messages in thread
From: Oleg Bartunov @ 2008-04-08 17:38 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
On Tue, 8 Apr 2008, Mark Stosberg wrote:
> Oleg Bartunov wrote:
>> 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,
>
> Thanks for the response. That sounds interesting, but it's not clear to me
> how I would put together a geo-spatial search calculating distances around
> the curvature of the earth using this technique. Is there is a SQL sample for
> this that you could point to?
it's not about calculating distances, but about searching objects around
given point.
>
> Also, I didn't recognize the names of the techniques you were benchmarking
> against "RADEC" and "Rtree", are either of these related to the
> "earthdistance" or "cube()" based searches I would have used already?
Rtree is a standard spatial tree, RADEC - is naive approach of
having two indexes, one on ra (right ascension) and another - on dec (declination).
Both are an astronomical coordinates.
>
> Mark
>
>
>
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-04-08 17:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-07 18:35 what worked: performance improvements for geo-spatial searching on FreeBSD Mark Stosberg <mark@summersault.com>
2008-04-07 18:47 ` Oleg Bartunov <oleg@sai.msu.su>
2008-04-08 13:45 ` Mark Stosberg <mark@summersault.com>
2008-04-08 17:38 ` Oleg Bartunov <oleg@sai.msu.su>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox