Index Problem? - Mailing list pgsql-performance

From Ron St-Pierre
Subject Index Problem?
Date
Msg-id 40800CC2.3050707@syscor.com
Whole thread Raw
Responses Re: Index Problem?
List pgsql-performance
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';

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



pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Poor performance of group by query
Next
From: "Jim C. Nasby"
Date:
Subject: Re: Horribly slow hash join