From: Ron St-Pierre <rstpierre@syscor.com>
To: pgsql-performance@postgresql.org
Subject: Index Problem?
Date: Fri, 16 Apr 2004 09:41:38 -0700
Message-ID: <40800CC2.3050707@syscor.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rstpierre@syscor.com
Subject: Re: Index Problem?
In-Reply-To: <40800CC2.3050707@syscor.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox