From: Chris <dmagick@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: update query taking too long
Date: Thu, 28 Jun 2007 16:16:50 +1000
Message-ID: <46835252.5070901@gmail.com> (raw)
In-Reply-To: <21072.1183009270@sss.pgh.pa.us>
References: <46834124.5010600@gmail.com>
<21072.1183009270@sss.pgh.pa.us>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dmagick@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: update query taking too long
In-Reply-To: <46835252.5070901@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox