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

Поиск
Список
Период
Сортировка
От Bruno Wolff III
Тема Re: optimizing a geo_distance() proximity query (example and benchmark)
Дата
Msg-id 20070206054011.GA9604@wolff.to
обсуждение исходный текст
Ответ на Re: optimizing a geo_distance() proximity query (example and benchmark)  (Mark Stosberg <mark@summersault.com>)
Ответы Re: explain analyze output for review (was: optimizing a geo_distance()...)  (Mark Stosberg <mark@summersault.com>)
Список pgsql-performance
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.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: How long should it take to insert 200,000 records?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: index scan through a subquery