Message-ID: <46835D14.6010507@archonet.com>
Date: Thu, 28 Jun 2007 08:02:44 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Chris <dmagick@gmail.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>
	<46835A16.3050602@gmail.com>
In-Reply-To: <46835A16.3050602@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Chris wrote:
> 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.

Well, 43 seconds to update 1 million rows suggests your real query 
should be complete in a few minutes, even if your real table has more 
columns.

Could you check again and just make sure you don't have a foreign key 
referencing this table? I suspect a large table without an index on the 
referencing column.

If you can't see anything, cancel the long-running query, run VACUUM 
FULL VERBOSE on the table, ANALYSE VERBOSE and then try it again. 
There's something very odd here.

-- 
   Richard Huxton
   Archonet Ltd