Received: from mail.heliosinc.net (64-51-53-162.client.dsl.net [64.51.53.162]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6QJpAf91621 for ; Thu, 26 Jul 2001 15:51:10 -0400 (EDT) (envelope-from ryan@heliosinc.net) Received: (qmail 20902 invoked from network); 26 Jul 2001 20:12:09 -0000 Received: from unknown (HELO ryan) (192.168.1.6) by 192.168.1.8 with SMTP; 26 Jul 2001 20:12:09 -0000 From: "Ryan Littrell" To: Subject: performance issue with distance function Date: Thu, 26 Jul 2001 13:04:00 -0700 Message-ID: <000001c1160e$1d435630$0601a8c0@ryan> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 X-Archive-Number: 200107/348 X-Sequence-Number: 4162 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. Ryan PS. Here are the respective execution plans: Merge Join (cost=0.00..3463985.82 rows=4342404 width=202) -> Nested Loop (cost=0.00..3461172.63 rows=14735 width=166) -> Nested Loop (cost=0.00..127378.88 rows=147350 width=162) -> Index Scan using restaurantszipcodeidx on restaurants r (cost=0.00..62.50 rows=500 width=138) -> Index Scan using locationszipcodeidx on locations l2 (cost=0.00..251.69 rows=295 width=24) -> Seq Scan on foodtyperestaurantidx ftr (cost=0.00..22.50 rows=10 width=4) -> Index Scan using locationszipcodeidx on locations l1 (cost=0.00..2260.63 rows=29470 width=36) Hash Join (cost=74.08..804150.11 rows=1447468 width=266) -> Nested Loop (cost=0.00..326410.91 rows=2894936 width=120) -> Index Scan using locationszipcodeidx on locations l2 (cost=0.00..251.69 rows=295 width=60) -> Seq Scan on locations l1 (cost=0.00..664.70 rows=29470 width=60) -> Hash (cost=73.96..73.96 rows=50 width=146) -> Merge Join (cost=67.58..73.96 rows=50 width=146) -> Sort (cost=22.67..22.67 rows=10 width=8) -> Seq Scan on foodtyperestaurantidx ftr (cost=0.00..22.50 rows=10 width=8) -> Sort (cost=44.91..44.91 rows=500 width=138) -> Seq Scan on restaurants r (cost=0.00..22.50 rows=500 width=138)