Re: optimizing pl/pgsql function - Mailing list pgsql-novice

From Masaru Sugawara
Subject Re: optimizing pl/pgsql function
Date
Msg-id 20020629222759.99C9.RK73@sea.plala.or.jp
Whole thread Raw
In response to optimizing pl/pgsql function  ("Uqtous" <Ubqtous@hotmail.com>)
List pgsql-novice
On Tue, 25 Jun 2002 17:46:51 -0400
"Uqtous" <Ubqtous@hotmail.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





pgsql-novice by date:

Previous
From: Andrew McMillan
Date:
Subject: Re: PostgreSQL setup on dual boot laptop
Next
From: "Brian Johnson"
Date:
Subject: Laptop configuration continued