Re: optimizing pl/pgsql function

On Tue, 25 Jun 2002 17:46:51 -0400
"Uqtous" <Ubqtous(at)hotmail(dot)com> wrote:

> I have a function that determines the distance between zip codes using
> latitude and longitude values. This function is called by a query that
> searches for members with x miles of the requesting member. The code works,
> but is very slow! Any optimization tips would be appreciated!

...

> SELECT INTO from_lat tlkpZip.zipLatitude::numeric FROM tlkpZip WHERE
> tlkpZip.zipCode=$1;
> SELECT INTO from_long tlkpZip.zipLongitude::numeric FROM tlkpZip WHERE
> tlkpZip.zipCode=$1;
> SELECT INTO to_lat tlkpZip.zipLatitude::numeric FROM tlkpZip WHERE
> tlkpZip.zipCode=$2;
> SELECT INTO to_long tlkpZip.zipLongitude::numeric FROM tlkpZip WHERE
> tlkpZip.zipCode=$2;

These operations seem to be a main cause of the time loss. In view of
the speed, it is better to reduce the frequencies of the table access.
As for what you would like to get there, the SELECTs in your function
can be moved into the SQL statement. But I don't have real data that
include zip codes, could you confirm the following for the performance
and appropriateness?

-- Function: zipdist(int4, int4, int4, int4)
CREATE FUNCTION zipdist(int4, int4, int4, int4) RETURNS numeric AS '
DECLARE
xcoord numeric;
ycoord numeric;
BEGIN
xcoord := ($1 - $2)::numeric;
ycoord := ($3 - $4)::numeric * cos($2 / 57.3::numeric)::numeric;
RETURN sqrt(xcoord * xcoord + ycoord * ycoord) * 69.1::numeric
END;
' LANGUAGE 'plpgsql';

-- If zipCode has no index
CREATE INDEX idx_tlkpZip_zipCode ON tlkpZip(zipCode);

SELECT t.*
FROM (SELECT tblmbr.*,
tlkpZip.zipLatitude,
tlkpZip.zipLongitude
FROM tblmbr LEFT OUTER JOIN tlkpZip USING(zipCode)
) AS t
WHERE zipdist(t.zipLatitude,
(SELECT zipLatitude FROM tlkpZip WHERE zipCode = 12345),
t.zipLongitude,
(SELECT zipLongitude FROM tlkpZip WHERE zipCode = 12345)
) <= 5
AND t.mbrid <> 1
ORDER BY t.mbrusername;

-- example of the output of EXPLAIN in 7.2.1
Sort (cost=303.52..303.52 rows=1667 width=52)
InitPlan
-> Index Scan using idx_tlkpzip_zipcode on tlkpzip
(cost=0.00..17.07 rows=5 width=4)
-> Index Scan using idx_tlkpzip_zipcode on tlkpzip
(cost=0.00..17.07 rows=5 width=4)
-> Merge Join (cost=69.83..214.33 rows=1667 width=52)
-> Index Scan using idx_tlkpzip_zipcode on tlkpzip
(cost=0.00..52.00 rows=1000 width=12)
-> Sort (cost=69.83..69.83 rows=1000 width=40)
-> Seq Scan on tblmbr
(cost=0.00..20.00 rows=1000 width=40)

Regards,
Masaru Sugawara