Hi,
I have a table with a lot of columns (text and integer).
It currently has 3Mio Rows.
Updating a column in all rows (integer) takes endless (days).
The column I update is not indexed.
How can I tune postgres to do this much more quickly?
VMstat looks like this:
r b swpd free buff cache si so bi bo in cs us
sy id wa
0 1 188 14160 16080 867064 0 0 880 888 168 479 1
2 0 97
1 1 188 15288 16080 865980 0 0 832 512 152 474 7
2 0 91
0 1 188 15464 16080 865348 0 0 872 592 144 461 2
1 0 97
so mostly iowait.
iostat shows about 10000 block writes per second.
My systems is debian-lenny (postgresql 8.3.5)
I already increased checkpoint_segments to 32, shared_buffers to 200MB
I also tried do disable autovacuum
here is a sample statement:
update users set price = (select price from prices where type =
'normal_price' and currency = users.currency)
(the table price only has 30 rows)
thank you very much!
sebastian