pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ryan Littrell <ryan@heliosinc.net>
To: pgsql-sql@postgresql.org
Subject: performance issue with distance function
Date: Thu, 26 Jul 2001 13:04:00 -0700
Message-ID: <000001c1160e$1d435630$0601a8c0@ryan> (raw)


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)





view thread (3+ messages)  latest in thread

Message-ID: <000001c1160e$1d435630$0601a8c0@ryan>
Permalink:  ../000001c1160e$1d435630$0601a8c0@ryan/
Also on:    postgresql.org/message-id/000001c1160e$1d435630$0601a8c0@ryan

 · 

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: ryan@heliosinc.net
  Subject: Re: performance issue with distance function
  In-Reply-To: <000001c1160e$1d435630$0601a8c0@ryan>

* 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