Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 969979FB2F4 for ; Mon, 5 Feb 2007 16:15:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 02315-09 for ; Mon, 5 Feb 2007 16:15:18 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id 692239FB1EA for ; Mon, 5 Feb 2007 16:15:17 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1238343ugf for ; Mon, 05 Feb 2007 12:15:15 -0800 (PST) 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=D22h++mkxPFalR2uHBu3qj03xodWYz0x2WlMupXiwJkyNngLjeRqmuDzsPr3utQLOdTtCXVUyv90j9wcu6HTfXboiI2Tdrv/i042FCyKpWthyD7VLAX5xTEfO3QlU9NFNOpu/ZMTvA4JXSN2S0GG0VJZUzppZ6wh3hqdt2mU6Cw= Received: by 10.78.165.16 with SMTP id n16mr97086hue.1170706515384; Mon, 05 Feb 2007 12:15:15 -0800 (PST) Received: by 10.78.45.3 with HTTP; Mon, 5 Feb 2007 12:15:15 -0800 (PST) Message-ID: Date: Mon, 5 Feb 2007 15:15:15 -0500 From: "Merlin Moncure" To: "Mark Stosberg" Subject: Re: optimizing a geo_distance() proximity query Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20070204051644.GA3754@wolff.to> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/50 X-Sequence-Number: 23104 On 2/5/07, Mark Stosberg wrote: > Bruno Wolff III wrote: > > On Sat, Feb 03, 2007 at 14:00:26 -0500, > > Mark Stosberg wrote: > >> I'm using geo_distance() from contrib/earthdistance would like to find a > >> way to spend up the geo distance calculation if possible. This is for a > >> proximity search: "Show me adoptable pets within 250 miles of this > >> zipcode". > > > > If you are using the "cube" based part of the earth distance package, > > then you can use gist indexes to speed those searches up. > > Thanks for the tip. Any idea what kind of improvement I can expect to > see, compared to using geo_distance()? a lot. be aware that gist takes longer to build than btree, but very fast to search. Index search and filter to box is basically an index lookup (fast!). for mostly static datasets that involve a lot of searching, gist is ideal. keep in mind that the cube based gist searches out a the smallest lat/lon 'square' projected onto the earth which covers your circular radius so you have to do extra processing if you want exact matches. ( you can speed this up to, by doing an 'inner box' search and not recomputing distance to those points) merlin