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=tZjs9iigCqCdOd7JLOCOn5BOaCpBLFvCbKOg38WOGw8GAt7qJfAefaQlnEPpshgdWlb6m3xGnTllP00nxS3rtnIrdbA8443f9TLWlF203rgMo5rnBbAIewKztmhI4C06Z4uJF4cTORVuZNAghTMgCHH52xuh1tarVftnbHlpepQ=
Message-ID: <46835252.5070901@gmail.com>
Date: Thu, 28 Jun 2007 16:16:50 +1000
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 2.0.0.4 (Windows/20070604)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: update query taking too long
References: <46834124.5010600@gmail.com> <21072.1183009270@sss.pgh.pa.us>
In-Reply-To: <21072.1183009270@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> Chris <dmagick@gmail.com> writes:
>> I'm trying to do an update of a reasonably large table and it's taking 
>> way too long so I'm trying to work out why and if I need to tweak any 
>> settings to speed it up.
> 
> Any foreign keys leading to or from that table?

Nope :(

> 3.5 million row updates are not exactly gonna be instantaneous anyway,
> but only FK checks or really slow user-written triggers would make it
> take upwards of an hour ...

No triggers, functions.

Table is pretty basic.

I have a few indexes (one on the primary key, one on emailaddress etc) 
but the 'domainname' column is a new one not referenced by any of the 
indexes.

FWIW (while the other update is still going in another window):

select SUBSTRING(emailaddress FROM POSITION('@' IN emailaddress)) from 
table;
Time: 28140.399 ms

Is there a better way to write the update? I thought about something 
like this (but couldn't get it working - guess I don't have the right 
syntax):

update t1 set domainname=(select id, SUBSTRING(emailaddress FROM 
POSITION('@' IN emailaddress)) from table t2) AS t2 where t1.id=t2.id

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