DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=TAwEqmcGgWGPMg6Xqny0JwUO6WBiYfCYvgJQS1bYEUPJiIrk43unVZ/QMnkAmLpMIeKS5PPTD8Glgl5JVl624CSiQiwe5zwxwSr8+6Ck0jsy2BLSM3M1GBNBczPQBuvCPO/tS96V39aXwH9NzSG/DxNncoocmVfRIlLWKdfEwxI=
Message-ID: <46836EC8.20807@gmail.com>
Date: Thu, 28 Jun 2007 18:18:16 +1000
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 2.0.0.4 (Windows/20070604)
MIME-Version: 1.0
To: Richard Huxton <dev@archonet.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>,
	Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: update query taking too long
References: <46834124.5010600@gmail.com> <21072.1183009270@sss.pgh.pa.us>
	<46835252.5070901@gmail.com> <468357B2.1040205@archonet.com>
	<46835A16.3050602@gmail.com> <46835D14.6010507@archonet.com>
In-Reply-To: <46835D14.6010507@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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/