| 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: | Whole Thread | Raw Message | 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
| 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 |