Re: update query taking too long

[Chris <dmagick(at)gmail(dot)com>]

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/