Re: Problems with an update-from statement and pg-8.1.4

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>