tip: faster sorting for proximity queries by using cube_distance()

From: Mark Stosberg <mark(at)summersault(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: tip: faster sorting for proximity queries by using cube_distance()
Date: 2007-02-07 21:30:16
Message-ID: eqdge4$291m$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,

I wanted to share something else I learned in my proximity search work.
One my requirements is to order by the distance that matches are found
from the center point.

When did this using earth_distance(), the benefit of the earth_box()
technique over the old geo_distance became minimal as I approached a
250mi radius.

Switching to sorting by cube_distance() offered a huge benefit, allowing
the earth_distance() query to run in about 100ms vs 300ms for the
geo_distance() equivalent.

I checked the results that cube_distance() produced versus
earth_distance(). cube_distance() is always (not surprisingly) a little
smaller, but the difference seems only grows to about a mile for a 250
mile radius. That's an acceptable margin of error for this application,
and may be for others as well.

Mark

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Benjamin Minshall 2007-02-08 16:48:01 stats collector process high CPU utilization
Previous Message Richard Huxton 2007-02-07 15:03:06 Re: Help Needed