Re: update query taking too long

Tom Lane wrote:
> Chris <dmagick(at)gmail(dot)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/