Skip site navigation (1) Skip section navigation (2)

Re: optimizing a geo_distance() proximity query (example and benchmark)

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Mark Stosberg <mark(at)summersault(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: optimizing a geo_distance() proximity query (example and benchmark)
Date: 2007-02-06 05:40:11
Message-ID: 20070206054011.GA9604@wolff.to (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Feb 05, 2007 at 18:01:05 -0500,
  Mark Stosberg <mark(at)summersault(dot)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.

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-02-06 06:52:25
Subject: Re: index scan through a subquery
Previous:From: Tom LaneDate: 2007-02-06 05:33:35
Subject: Re: How long should it take to insert 200,000 records?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group