From: Jeff Hoffmann <jeff@propertykey.com>
To: Ryan Littrell <ryan@heliosinc.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: performance issue with distance function
Date: Thu, 26 Jul 2001 16:20:07 -0500
Message-ID: <3B608987.1AEF83A6@propertykey.com> (raw)
References: <000001c1160e$1d435630$0601a8c0@ryan>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeff@propertykey.com, ryan@heliosinc.net
Subject: Re: performance issue with distance function
In-Reply-To: <3B608987.1AEF83A6@propertykey.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox