pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (3+ messages)  latest in thread

Message-ID: <3B608987.1AEF83A6@propertykey.com>
Permalink:  ../3B608987.1AEF83A6@propertykey.com/
Also on:    postgresql.org/message-id/3B608987.1AEF83A6@propertykey.com

 · 

reply

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