Re: optimizing pl/pgsql function
От | Masaru Sugawara |
---|---|
Тема | Re: optimizing pl/pgsql function |
Дата | |
Msg-id | 20020629222759.99C9.RK73@sea.plala.or.jp обсуждение исходный текст |
Ответ на | optimizing pl/pgsql function ("Uqtous" <Ubqtous@hotmail.com>) |
Список | 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 по дате отправления: