Message-ID: <46B8749F.8010002@archonet.com>
Date: Tue, 07 Aug 2007 14:33:19 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Mark Makarowsky <bedrockconstruction@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update table performance
References: <435532.80672.qm@web32210.mail.mud.yahoo.com>
In-Reply-To: <435532.80672.qm@web32210.mail.mud.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mark Makarowsky wrote:
> I have a table with 4,889,820 records in it.  The
> table also has 47 fields.  I'm having problems with
> update performance.  Just as a test, I issued the
> following update:
> 
> update valley set test='this is a test'
> 
> This took 905641 ms.  Isn't that kind of slow?

The limiting factor here will be how fast you can write to your disk. 
Let's see: 5 million rows in ~900 seconds, that's about 5500 
rows/second. Now, you don't say  how large your rows are, but assuming 
each row is say 1kB that'd be 5.5MB/sec - or not brilliant. Simplest way 
to find out total activity is check how much disk space PG is using 
before and after the update.

What you'll need to do is monitor disk activity, in particular how many 
writes and how much time the processor spends waiting on writes to complete.

If your standard usage pattern is to update a single field in all rows 
of a large table, then performance isn't going to be sub-second I'm afraid.

-- 
   Richard Huxton
   Archonet Ltd