Message-ID: <45772022.4050303@blackducksoftware.com>
Date: Wed, 06 Dec 2006 14:55:14 -0500
From: Ted Allen <tallen@blackducksoftware.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060922)
MIME-Version: 1.0
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
CC: Rafael Martinez <r.m.guerrero@usit.uio.no>,
	pgsql-performance@postgresql.org
Subject: Re: Problems with an update-from statement and  pg-8.1.4
References: <1165432244.9606.18.camel@bbking.linux>
	<20061206111630.R8187@megazone.bigpanda.com>
In-Reply-To: <20061206111630.R8187@megazone.bigpanda.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Stephan Szabo wrote:
> On Wed, 6 Dec 2006, Rafael Martinez wrote:
>
>   
>> We are having some problems with an UPDATE ... FROM sql-statement and
>> pg-8.1.4. It takes ages to finish. The problem is the Seq Scan of the
>> table 'mail', this table is over 6GB without indexes, and when we send
>> thousands of this type of statement, the server has a very high iowait
>> percent.
>>
>> How can we get rid of this Seq Scan?
>>
>> I send the output of an explain and table definitions:
>> -------------------------------------------------------------------------
>>
>> mailstats=# EXPLAIN update mail SET spamscore = '-5.026'  FROM mail m,
>> mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id =
>> '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47';
>>     
>
> I don't think this statement does what you expect. You're ending up with
> two copies of mail in the above one as "mail" and one as "m". You probably
> want to remove the mail m in FROM and use mail rather than m in the
> where clause.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
>   
Worse yet I think your setting "spamcore" for EVERY row in mail to 
'-5.026'.  The above solution should fix it though.

-- Ted

*
* <http://www.blackducksoftware.com>