From: | Jeff Hoffmann <jeff(at)propertykey(dot)com> |
---|---|
To: | Ryan Littrell <ryan(at)heliosinc(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: performance issue with distance function |
Date: | 2001-07-26 21:20:07 |
Message-ID: | 3B608987.1AEF83A6@propertykey.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2001-07-26 21:50:14 | Re: Restriction by grouping problem. |
Previous Message | Jeff Barrett | 2001-07-26 21:09:24 | Restriction by grouping problem. |