From: Erik Jones <erik@myemma.com>
To: Rafael Martinez <r.m.guerrero@usit.uio.no>
Cc: Ted Allen <tallen@blackducksoftware.com>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problems with an update-from statement and pg-8.1.4
Date: Wed, 06 Dec 2006 14:19:38 -0600
Message-ID: <457725DA.90402@myemma.com> (raw)
In-Reply-To: <1165435845.9606.26.camel@bbking.linux>
References: <1165432244.9606.18.camel@bbking.linux>
<20061206111630.R8187@megazone.bigpanda.com>
<45772022.4050303@blackducksoftware.com>
<1165435845.9606.26.camel@bbking.linux>
Rafael Martinez wrote:
> On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote:
>
>> Stephan Szabo wrote:
>>
>>> On Wed, 6 Dec 2006, Rafael Martinez wrote:
>>>
>>>> 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.
>>>
>>>
>>>
>> Worse yet I think your setting "spamcore" for EVERY row in mail to
>> '-5.026'. The above solution should fix it though.
>>
>> -- Ted
>>
>>
>
> Thanks for the answers. I think the 'problem' is explain in the
> documentation:
>
> "fromlist
>
> A list of table expressions, allowing columns from other tables to
> appear in the WHERE condition and the update expressions. This is
> similar to the list of tables that can be specified in the FROMClause of
> a SELECT statement. Note that the target table must not appear in the
> fromlist, unless you intend a self-join (in which case it must appear
> with an alias in the fromlist)".
>
> And as you said, we can not have 'mail m' in the FROM clause. I have
> contacted the developers and they will change the statement. I gave then
> these 2 examples:
>
> -------------------------------------------------------------------------------
> mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM
> mail_received mr where mr.mail_id = mail.mail_id AND mr.queue_id =
> '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47';
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.00..6.54 rows=1 width=57)
> -> Index Scan using received_queue_id_index on mail_received mr
> (cost=0.00..3.20 rows=1 width=4)
> Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text)
> Filter: (mailhost = '129.240.10.47'::inet)
> -> Index Scan using mail_pkey on mail (cost=0.00..3.32 rows=1
> width=57)
> Index Cond: ("outer".mail_id = mail.mail_id)
> (6 rows)
>
> mailstats=# explain update mail SET spamscore = '-5.026' where mail_id
> = (select mail_id from mail_received where queue_id = '1GrxLs-0004N9-I1'
> and mailhost = '129.240.10.47');
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------
> Index Scan using mail_pkey on mail (cost=3.20..6.52 rows=1 width=57)
> Index Cond: (mail_id = $0)
> InitPlan
> -> Index Scan using received_queue_id_index on mail_received
> (cost=0.00..3.20 rows=1 width=4)
> Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text)
> Filter: (mailhost = '129.240.10.47'::inet)
> (6 rows)
> -------------------------------------------------------------------------------
>
Look again at the estimated costs of those two query plans. You haven't
gained anything there. Try this out:
EXPLAIN UPDATE mail
SET spamscore = '-5.026'
FROM mail_received mr
WHERE mail.mail_id = mr.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' ;
--
erik jones <erik@myemma.com>
software development
emma(r)
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: erik@myemma.com, r.m.guerrero@usit.uio.no, tallen@blackducksoftware.com, sszabo@megazone.bigpanda.com
Subject: Re: Problems with an update-from statement and pg-8.1.4
In-Reply-To: <457725DA.90402@myemma.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