From: Richard Huxton <dev@archonet.com>
To: Mark Makarowsky <bedrockconstruction@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update table performance
Date: Tue, 07 Aug 2007 14:33:19 +0100
Message-ID: <46B8749F.8010002@archonet.com> (raw)
In-Reply-To: <435532.80672.qm@web32210.mail.mud.yahoo.com>
References: <435532.80672.qm@web32210.mail.mud.yahoo.com>
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
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: dev@archonet.com, bedrockconstruction@yahoo.com
Subject: Re: Update table performance
In-Reply-To: <46B8749F.8010002@archonet.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