Re: Updates are slow..

"Tom Burke" <lists(at)spamex(dot)com> writes:
> For the purposes of our performance test, we created a PostgreSQL
> database with only a single table of about 1.2 million records. Updating
> an indexed column for 600K records on that table is unusually slow. It
> takes anywhere from forty minutes to over an hour.

That seems way way off. I tried to replicate your situation on an RH
7.2 box, using PG 7.1 (I don't have 7.2 installed there at the moment,
and current devel sources might be an unfair comparison). I got a time
more like forty seconds:

test71=# create table emp (emp_id serial, dept_id int, emp_status int);
test71=# create index emp2 on emp(DEPT_ID, EMP_STATUS);
[ fill with random data ]

[tgl(at)rh1 tgl]$ time psql test71 -c "UPDATE Emp SET Dept_ID = 5 WHERE Dept_ID =
4;"
UPDATE 593216

real 0m41.290s
user 0m0.000s
sys 0m0.000s
[tgl(at)rh1 tgl]$

Now this machine has a faster CPU than yours (1.8GHz I think), but
probably not any faster disk. At best it could be twice as fast as
yours. I don't have as many columns either, so the I/O volume is
probably a good bit more in your case. But even allowing for that,
it's hard to extrapolate to an hour.

What are the datatypes of your columns, exactly? What's the average
tuple size (actually, showing the VACUUM VERBOSE stats for the table
would be the most useful answer)? Are you *sure* there are no foreign
keys either from or to this table? Also, what plan is shown by EXPLAIN
for the query? (7.2 should surely not be dumb enough to pick an
indexscan plan, but if it did that would explain a lot ...)

regards, tom lane