From: Jochem van Dieten <jochemd@oli.tudelft.nl>
To: rstpierre@syscor.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Index Problem?
Date: Sat, 17 Apr 2004 14:25:40 +0200
Message-ID: <40812244.5080001@oli.tudelft.nl> (raw)
References: <40800CC2.3050707@syscor.com>
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
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: jochemd@oli.tudelft.nl, rstpierre@syscor.com
Subject: Re: Index Problem?
In-Reply-To: <40812244.5080001@oli.tudelft.nl>
* 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