From: Richard Huxton <dev@archonet.com>
To: Chris <dmagick@gmail.com>
Cc: 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 07:39:46 +0100
Message-ID: <468357B2.1040205@archonet.com> (raw)
In-Reply-To: <46835252.5070901@gmail.com>
References: <46834124.5010600@gmail.com>
<21072.1183009270@sss.pgh.pa.us>
<46835252.5070901@gmail.com>
Chris wrote:
> Tom Lane wrote:
>> 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.
Of course you really want a trigger on this, since presumably domainname
should always be kept in sync with emailaddress. But that's not the
immediate issue.
> 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):
What's saturated? Is the system I/O limited or CPU limited? You *should*
be limited by the write speed of your disk with something simple like this.
What happens if you do the following?
CREATE TABLE email_upd_test (id SERIAL, email text, domainname text,
PRIMARY KEY (id));
INSERT INTO email_upd_test (email) SELECT n::text || '@' || n::text FROM
(SELECT generate_series(1,1000000) AS n) AS numbers;
ANALYSE email_upd_test;
\timing
UPDATE email_upd_test SET domainname=substring(email from position('@'
in email));
UPDATE 1000000
Time: 35056.125 ms
That 35 seconds is on a simple single-disk IDE disk. No particular
tuning done on that box.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, dmagick@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: update query taking too long
In-Reply-To: <468357B2.1040205@archonet.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