Re: performance issue with distance function
От | Jeff Hoffmann |
---|---|
Тема | Re: performance issue with distance function |
Дата | |
Msg-id | 3B608987.1AEF83A6@propertykey.com обсуждение исходный текст |
Ответ на | performance issue with distance function ("Ryan Littrell" <ryan@heliosinc.net>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: