DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=npRsTlql8hoYgjl32xmNon7jE5XyxiIsWbFzqvO2aG/igRUu86UtMLIv66XFYMS34Z87bGNeEypJ4LSYhXc5leTK/YR0TqFY47N15yZnmNUOZ+R26A5gVnQWXteIE/EFe2trypB1wb4RZFhOEPiEzScir2XbsgaXotuku/XTx54=
Message-ID: <b42b73150702060458v628126bei9ce1eefcafe3aa72@mail.gmail.com>
Date: Tue, 6 Feb 2007 18:28:29 +0530
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "Mark Stosberg" <mark@summersault.com>
Subject: Re: optimizing a geo_distance() proximity query (example and
	benchmark)
Cc: pgsql-performance@postgresql.org
In-Reply-To: <eq8d0b$26cd$1@news.hub.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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 2/6/07, 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.
>
> When I benchmarked this query against the old geo_distance() variation,
> it was about 200 times faster (~100ms vs .5ms).
>
> 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.
>
> Could someone post an example of how to further refine this so the
> results more closely match what geo_distance returns() ?

I agree with bruno...the extra time is probably  not what you are
thinking...please post explain analyze results, etc.  However bruno's
ratio, while correct does not tell the whole story because you have to
recheck distance to every point in the returned set.

There is a small optimization you can make.  The query you wrote
automatically excludes points within a certain box.  you can also
include points in the set which is the largest box that fits in the
circle:

select * from zipcodes
where
earth_box('(436198.322855334,
4878562.8732218, 4085386.43843934)'::cube,inner_radius) @ earth_coords
or
(
earth_box('(436198.322855334,
4878562.8732218, 4085386.43843934)'::cube,16093.44) @ earth_coords
and
geo_dist...
);

you can also choose to omit the earth_coords column and calculate it
on the fly...there is no real performance hit for this but it does
make the sql a bit ugly.

merlin