Received: from www.propertykey.com (propertykey.com [206.196.37.193]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6QLK6f25782 for ; Thu, 26 Jul 2001 17:20:07 -0400 (EDT) (envelope-from jeff@propertykey.com) Received: from propertykey.com (gotojail.propertykey.com [206.196.37.197]) by www.propertykey.com (8.9.3/8.9.3) with ESMTP id QAA03481; Thu, 26 Jul 2001 16:20:02 -0500 Message-ID: <3B608987.1AEF83A6@propertykey.com> Date: Thu, 26 Jul 2001 16:20:07 -0500 From: Jeff Hoffmann Organization: PropertyKey.com X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U) X-Accept-Language: en MIME-Version: 1.0 To: Ryan Littrell CC: pgsql-sql@postgresql.org Subject: Re: performance issue with distance function References: <000001c1160e$1d435630$0601a8c0@ryan> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200107/349 X-Sequence-Number: 4163 Ryan Littrell wrote: > > I am trying to execute the following command: > > SELECT R.*, distance(L1.lat, L1.lon, L2.lat, L2.lon) AS Distance > FROM Restaurants R, Locations L1, Locations L2, FoodTypeRestaurantIDX FTR > WHERE R.Zipcode=L1.Zipcode AND L2.Zipcode = '93705' AND R.Delivery=true AND > R.RestaurantID=FTR.RestaurantID AND FTR.FoodTypeID=1 AND distance(L1.lat, > L1.lon, L2.lat, L2.lon) <= 60 > LIMIT 100 OFFSET 0 > > I would rather execute this command: (but i get the error "Attribute > 'distance' not found") > > SELECT R.*, distance(L1.lat, L1.lon, L2.lat, L2.lon) AS Distance > FROM Restaurants R, Locations L1, Locations L2, FoodTypeRestaurantIDX FTR > WHERE R.Zipcode=L1.Zipcode AND L2.Zipcode = '93705' AND R.Delivery=true AND > R.RestaurantID=FTR.RestaurantID AND FTR.FoodTypeID=1 AND distance <= 60 > LIMIT 100 OFFSET 0 > > Having that second distance function in the "WHERE" section of my sql > statement is costing me at least 10-20 seconds of execution time. I am > looking for a solution that will speed this up. Does anyone have any advice. > Thanks in advance. > this probably isn't what you want, but would it speed things up if you did an "order by distance" instead of doing the "distance <= 60", then having your application cut the results at 60? that should work from a language perspective, at least, and if the distance function is pretty computationally intensive, it should help. -- Jeff Hoffmann PropertyKey.com