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





view thread (13+ messages)  latest in thread

Message-ID: <40800CC2.3050707@syscor.com>
Permalink:  ../40800CC2.3050707@syscor.com/
Also on:    postgresql.org/message-id/40800CC2.3050707@syscor.com

 · 

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: 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