Re: update query taking too long

Richard Huxton wrote:
> Chris wrote:
>> db=# UPDATE email_upd_test SET domainname=substring(email from
>> position('@' in email));
>> UPDATE 1000000
>> Time: 43796.030 ms
>>
>> I think I'm I/O bound from my very limited understanding of vmstat.
>
> Well, 43 seconds to update 1 million rows suggests your real query
> should be complete in a few minutes, even if your real table has more
> columns.

Yep.

I think I have solved it though - the server was checkpointing so much
not much else was going on.

I didn't have logging set up before but it's up and running now and I
was getting

LOG: checkpoints are occurring too frequently (26 seconds apart)
HINT: Consider increasing the configuration parameter
"checkpoint_segments".

So I increased that from 10 to 30 and it finished:

UPDATE 3500101
Time: 146513.349 ms

Thanks for all the help :)

--
Postgresql & php tutorials
http://www.designmagick.com/