From: Tom Lane <tgl@sss.pgh.pa.us>
To: John A Meinel <john@arbash-meinel.com>
Cc: Matthew Schumacher <matt.s@aptalaska.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0
Date: Thu, 04 Aug 2005 12:16:10 -0400
Message-ID: <16345.1123172170@sss.pgh.pa.us> (raw)
In-Reply-To: <42F22F59.7090901@arbash-meinel.com>
References: <42E9749F.6000709@aptalaska.net>
<1122616211.5691.340.camel@lamb.mcmillan.net.nz>
<42EA6966.8090402@aptalaska.net>
<1122668371.5691.352.camel@lamb.mcmillan.net.nz>
<19405.1122670917@sss.pgh.pa.us>
<42EAA410.9010700@aptalaska.net>
<27897.1122748133@sss.pgh.pa.us>
<42EE8043.1050207@aptalaska.net>
<op.suulgxokth1vuj@localhost>
<42EE93F8.5010003@aptalaska.net>
<20050801214931.GF60019@decibel.org>
<42EE9D83.8000405@pobox.com>
<42F1CE43.8040801@aptalaska.net>
<15610.1123166222@sss.pgh.pa.us>
<42F22F59.7090901@arbash-meinel.com>
John A Meinel <john@arbash-meinel.com> writes:
> Tom Lane wrote:
>> I don't really see why you think that this path is going to lead to
>> better performance than where you were before.
> So for an IN (sub-select), does it actually pull all of the rows from
> the other table, or is the planner smart enough to stop once it finds
> something?
It stops when it finds something --- but it's still a join operation
in essence. I don't see that putting the values one by one into a table
and then joining is going to be a win compared to just processing the
values one at a time against the main table.
> Is IN (sub-select) about the same as EXISTS (sub-select WHERE x=y)?
> What about NOT IN (sub-select) versus NOT EXISTS (sub-select WHERE x=y)
The EXISTS variants are actually worse, because we've not spent as much
time teaching the planner how to optimize them. There's effectively
only one decent plan for an EXISTS, which is that the subselect's "x" is
indexed and we do an indexscan probe using the outer "y" for each outer
row. IN and NOT IN can do that, or several alternative plans that might
be better depending on data statistics.
However, that's cold comfort for Matthew's application -- the only way
he'd get any benefit from all those planner smarts is if he ANALYZEs
the temp table after loading it and then EXECUTEs the main query (so
that it gets re-planned every time). Plus, at least some of those
alternative plans would require an index on the temp table, which is
unlikely to be worth the cost of setting up. And finally, this
formulation requires separate IN and NOT IN tests that are necessarily
going to do a lot of redundant work.
There's enough overhead here that I find it highly doubtful that it'll
be a win compared to the original approach of retail queries against the
main table.
regards, tom lane
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: tgl@sss.pgh.pa.us, john@arbash-meinel.com, matt.s@aptalaska.net
Subject: Re: Performance problems testing with Spamassassin 3.1.0
In-Reply-To: <16345.1123172170@sss.pgh.pa.us>
* 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