From: Scott Marlowe <scott.marlowe@gmail.com>
To: Mark Makarowsky <bedrockconstruction@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update table performance
Date: Tue, 7 Aug 2007 09:53:31 -0500
Message-ID: <dcc563d10708070753t6ff8b988x7ec153bea644b7d0@mail.gmail.com> (raw)
In-Reply-To: <435532.80672.qm@web32210.mail.mud.yahoo.com>
References: <435532.80672.qm@web32210.mail.mud.yahoo.com>
On 8/7/07, Mark Makarowsky <bedrockconstruction@yahoo.com> 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? There
> aren't any indexes, triggers, constraints or anything
> on this table. The version of Postgres is "PostgreSQL
> 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe
> (GCC) 3.4.2 (mingw-special)". The operating
> environment is Windows 2003 Standard Edition w/service
> pack 2. It is 2.20 Ghz with 1.0 GB of RAM. Here is
> the results from Explain:
>
> "Seq Scan on valley (cost=0.00..1034083.57
> rows=4897257 width=601)"
Have you done this a few times? You could easily have a very large
and bloated table if you do this several times in a row. That would
explain the slow performance. If you're going to do a lot of updates
without where clauses on large tables, you'll need to run a vacuum
right afterwards to clean things up.
I see that you included a lot about your machine, but you didn't
include any specs on your disk subsystem. When it comes to update
speed, the disk subsystem is probably the most important part.
Note also that Windows is still not the preferred platform for
postgresql from a performance perspective (actually, the only database
where that's true is MS-SQL really).
Have you run any benchmarks on your disk subsystem to see how fast it is?
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: scott.marlowe@gmail.com, bedrockconstruction@yahoo.com
Subject: Re: Update table performance
In-Reply-To: <dcc563d10708070753t6ff8b988x7ec153bea644b7d0@mail.gmail.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