optimizing pl/pgsql function
От | Uqtous |
---|---|
Тема | optimizing pl/pgsql function |
Дата | |
Msg-id | OE52L9BGTuAyHsjCGvs0000016c@hotmail.com обсуждение исходный текст |
Ответы |
Re: optimizing pl/pgsql function
|
Список | pgsql-novice |
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! I'm running PG 7.1.3... -- Function: zipdist(int4, int4) CREATE FUNCTION "zipdist"("int4", "int4") RETURNS "numeric" AS 'DECLARE from_lat NUMERIC; from_long NUMERIC; to_lat NUMERIC; to_long NUMERIC; xcoord NUMERIC; ycoord NUMERIC; BEGIN 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; xcoord:=(69.1::numeric*(to_lat::numeric-from_lat::numeric)::numeric); ycoord:=(69.1::numeric*((to_long::numeric-from_long::numeric)::numeric*cos(f rom_lat/57.3::numeric)::numeric)); RETURN sqrt((xcoord::numeric*xcoord::numeric)::numeric+(ycoord::numeric*ycoord::num eric)::numeric)::numeric; END;' LANGUAGE 'plpgsql'; And the query that calls the function; "12345" is the requesting member's zip, "5" is the preferred distance, and "1" is the requesting member's ID: SELECT tblmbr.* FROM tblmbr WHERE zipdist(12345,tblmbr.mbrzipcode)<=5 AND tblmbr.mbrid <> 1 ORDER BY tblmbr.mbrusername;
В списке pgsql-novice по дате отправления: