Re: [PERFORM] Index Problem?

[Jochem van Dieten]

Ron St-Pierre wrote:
I am using postgres 7.4.1 and have a problem with a plpgsql function. 
When I run the function on the production server it takes approx 33 
minutes to run. I dumped the DB and copied it to a similarly configured 
box and ran the function and it ran in about 10 minutes. Can anyone 
offer advice on tuning the function or my database? Here are the 
lengthy, gory details.

F u n c t i o n
It updates seven columns of a table 1 to 4 times daily. Current data = 
42,000 rows, new data = 30,000 rows.

   CREATE TYPE employeeType AS (empID INTEGER, updateDate DATE, bDate 
INTEGER, val1 NUMERIC, val2 NUMERIC, val3 NUMERIC, val4 NUMERIC, favNum 
NUMERIC);

   CREATE OR REPLACE FUNCTION updateEmployeeData() RETURNS SETOF 
employeeType AS '
       DECLARE
           rec     RECORD;
       BEGIN
           FOR rec IN SELECT empID, updateDate, bDate, val1, val2, val3, val4, favNum FROM newData LOOP
               RETURN NEXT rec;
               UPDATE currentData SET val1=rec.val1, val2=rec.val2, val3=rec.val2, val4=rec.val4, favNum=rec.favNum, updateDate=rec.updateDate
               WHERE empID=rec.empID;
           END LOOP;
           RETURN;
       END;
   ' LANGUAGE 'plpgsql';
Can't you handle this with a simple update query?

UPDATE
        currentData
SET
        val1 = newData.val1,
        val2 = newData.val2,
        val3 = newData.val3,
        val4 = newData.val4,
        favNum = newData.favNum,
        updateDate = newData.updateDate
FROM
        newData
WHERE
        newDate.empID = currentData.empID
Jochem

--
I don't get it
immigrants don't work
and steal our jobs
    - Loesje


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org