From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: 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, 6 Dec 2006 11:20:44 -0800 (PST)
Message-ID: <20061206111630.R8187@megazone.bigpanda.com> (raw)
In-Reply-To: <1165432244.9606.18.camel@bbking.linux>
References: <1165432244.9606.18.camel@bbking.linux>
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.
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: 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: <20061206111630.R8187@megazone.bigpanda.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