On 8/7/07, Mark Makarowsky <bedrockconstruction@xxxxxxxxx> 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? ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
