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=Z/6jH9nZQX/TWI7WFfdEN8RnU4GEpcdQAG6mrLHVp4V5or3EozYy95q0sR6L9oUCBdKfqWQF629NciOWcCO/Rkd9ffOiVdQRvF4dxOvPaRUVHKWxclCjLE7EVhx1X06dAdCRvO6HK11XoHVqmk3AqaIJrVirxD4P97ZpbAkWP+8=
Message-ID: <46835A16.3050602@gmail.com>
Date: Thu, 28 Jun 2007 16:49:58 +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>
In-Reply-To: <468357B2.1040205@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Richard Huxton wrote:
> 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?

db=# CREATE TABLE email_upd_test (id SERIAL, email text, domainname 
text, PRIMARY KEY (id));
NOTICE:  CREATE TABLE will create implicit sequence 
"email_upd_test_id_seq" for serial column "email_upd_test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"email_upd_test_pkey" for table "email_upd_test"
CREATE TABLE
Time: 276.500 ms
db=# INSERT INTO email_upd_test (email) SELECT n::text || '@' || n::text 
FROM (SELECT generate_series(1,1000000) AS n) AS numbers;
INSERT 0 1000000
Time: 14104.663 ms
db=# ANALYSE email_upd_test;
ANALYZE
Time: 121.775 ms
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.

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