To: John A Meinel <john@arbash-meinel.com>
Cc: Matthew Schumacher <matt.s@aptalaska.net>,
 pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0 
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>
Comments: In-reply-to John A Meinel <john@arbash-meinel.com>
	message dated "Thu, 04 Aug 2005 10:08:09 -0500"
Date: Thu, 04 Aug 2005 12:16:10 -0400
Message-ID: <16345.1123172170@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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