Re: Update table performance

On 8/7/07, Mark Makarowsky <bedrockconstruction(at)yahoo(dot)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?