optimizing pl/pgsql function

[Guest_imported]

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!

-- 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(from_lat/57.3::numeric)::numeric));	

RETURN sqrt((xcoord::numeric*xcoord::numeric)::numeric+(ycoord::numeric*ycoord::numeric)::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;

 

[rycamor]

Are you sure it's the function that is causing your speed problem? This may be a silly question, but have you indexed the table holding all the zip code data?

Also, I notice that you are using your function as the WHERE clause in a query. If you think about it, that means the function has to be applied to every single row in the table, to ascertain which rows meet the condition. (Unless I misread your table design). This can be viewed as your "inner loop", and it is fairly computationally intensive.

You have designed a perfectly valid function, but you might want to re-think your overall structure, in order to optimize things. Also, you might want to think of some sort of "first pass" optimization to roughly discard the majority of zip codes that you know would fall outside of the query range, and then apply your trig function to the rest to get the actual results. -------------------------------------------

"Now, this might cause some discomfort..."
(

http://www.wired.com/news/politics/0,1283,51274,00.html)

 

[Guest_imported]

I had someone recommend the following, but it did not fare much better, in fact I got a larger recordset than I should have:

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

And in the calling code:

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