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';
The emp table has 60 columns, all indexed, about two-thirds are numeric, but they are not affected by this update. The other 50+ columns are updated in the middle of the night and the amount of time that update takes isn't a concern.
Late last night I dumped the table, dropped it and re-created it from the dump (on the production server - when no one was looking). When I re-ran the function it took almost 11 minutes, which was pretty much in line with my results from the dev server.
D e t a i l s
v 7.4.1
Debian stable
1 GB ram
shared_buffers = 2048
sort_mem = 1024
SHMMAX 360000000 (360,000,000)
VACUUM FULL ANALYZE is run every night, and I ran it yesterday between running the function and it made no difference in running time.
top shows the postmaster using minimal cpu (0-40%) and miniscule memory. vmstat shows a fair amount of IO (bo=1000->4000).
Yesterday on the dev server we upgraded to the 2.6 kernel and unfortunately only noticed a small increase in update time (about one minute).
So does anyone have any suggestions for me on speeding this up? Is it the index? The function is run daily during the mid afternoon to early evening and really drags the performance of the server down (it also hosts a web site).
Thanks Ron
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
