pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (13+ messages)

Message-ID: <40812244.5080001@oli.tudelft.nl>
Permalink:  ../40812244.5080001@oli.tudelft.nl/
Also on:    postgresql.org/message-id/40812244.5080001@oli.tudelft.nl

 · 

reply

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