From: Ted Allen <tallen@blackducksoftware.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Rafael Martinez <r.m.guerrero@usit.uio.no>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problems with an update-from statement and pg-8.1.4
Date: Wed, 06 Dec 2006 14:55:14 -0500
Message-ID: <45772022.4050303@blackducksoftware.com> (raw)
In-Reply-To: <20061206111630.R8187@megazone.bigpanda.com>
References: <1165432244.9606.18.camel@bbking.linux>
<20061206111630.R8187@megazone.bigpanda.com>
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;
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: tallen@blackducksoftware.com, sszabo@megazone.bigpanda.com, r.m.guerrero@usit.uio.no
Subject: Re: Problems with an update-from statement and pg-8.1.4
In-Reply-To: <45772022.4050303@blackducksoftware.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