Re: Index Problem?

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