Skip site navigation (1) Skip section navigation (2)

Re: optimizing pl/pgsql function

From: Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp>
To: "Uqtous" <Ubqtous(at)hotmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: optimizing pl/pgsql function
Date: 2002-06-29 13:29:09
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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 '
   xcoord numeric;
   ycoord numeric;
   xcoord := ($1 - $2)::numeric;
   ycoord := ($3 - $4)::numeric * cos($2 / 57.3::numeric)::numeric;
   RETURN sqrt(xcoord * xcoord + ycoord * ycoord) * 69.1::numeric
' LANGUAGE 'plpgsql';

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

FROM (SELECT tblmbr.*,
        FROM tblmbr LEFT OUTER JOIN tlkpZip USING(zipCode)
     ) AS t
WHERE zipdist(t.zipLatitude,
              (SELECT zipLatitude FROM tlkpZip WHERE zipCode = 12345),
              (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)
    ->  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)

Masaru Sugawara

In response to

pgsql-novice by date

Next:From: Brian JohnsonDate: 2002-06-30 03:42:21
Subject: Laptop configuration continued
Previous:From: Aarni Ruuhimäki / Megative Tmi / KYMI.comDate: 2002-06-29 07:18:08
Subject: Parser function / command again ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group