From: Bruno Wolff III <bruno@wolff.to>
To: Mark Stosberg <mark@summersault.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: optimizing a geo_distance() proximity query (example and benchmark)
Date: Mon, 5 Feb 2007 23:40:11 -0600
Message-ID: <20070206054011.GA9604@wolff.to> (raw)
In-Reply-To: <eq8d0b$26cd$1@news.hub.org>
References: <eq2m5o$8cr$1@sea.gmane.org>
<20070204051644.GA3754@wolff.to>
<eq81l5$2na3$1@news.hub.org>
<b42b73150702051215s6fe341e2re43872ab84abb185@mail.gmail.com>
<eq8d0b$26cd$1@news.hub.org>
On Mon, Feb 05, 2007 at 18:01:05 -0500,
Mark Stosberg <mark@summersault.com> wrote:
>
> It's also notable that the units used are meters, not miles like
> geo_distance(). That's what the magic number of "16093.44" is-- 10 miles
> converted to meters.
You can change the earth() function in earthdistance.sql before running it
to use some other unit other than meters:
-- earth() returns the radius of the earth in meters. This is the only
-- place you need to change things for the cube base distance functions
-- in order to use different units (or a better value for the Earth's radius).
CREATE OR REPLACE FUNCTION earth() RETURNS float8
LANGUAGE 'sql' IMMUTABLE
AS 'SELECT ''6378168''::float8';
> However, my next step was to try a more "real world" query that involved
> a more complex where clause and a couple of table joins. So far, that
> result is coming out /slower/ with the new approach, even though the
> index is being used. I believe this may be cause of the additional
> results found that are outside of the sphere, but inside the cube. This
> causes additional rows that need processing in the joined tables.
This is unlikely to be the cause. The ratio of the area of the cube to
the circle for small radii (compared to the radius of the earth, so that
we can consider thinsg flat) is 4/pi = 1.27 which shouldn't cause that
much of a change.
It might be that you are getting a bad plan. The guess on the selectivity
of the gist constraint may not be very good.
Some people here may be able to tell you more if you show us explain
analyze output.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruno@wolff.to, mark@summersault.com
Subject: Re: optimizing a geo_distance() proximity query (example and benchmark)
In-Reply-To: <20070206054011.GA9604@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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