Lincoln Yeoh <lylyeoh@mecomb.com> writes:
> At 11:18 AM 17-05-2000 -0400, Tom Lane wrote:
>> In fact, IN (subselect), INTERSECT, and EXCEPT are all pretty much the
>> same thing, and they're all pretty slow in the current code :-(, because
>> they all work by rescanning the inner query for each outer tuple --- in
>> other words, they're all implemented like plain nestloop joins. EXISTS
>> is marginally better because the planner can figure out how to use an
>> index on the inner table, if there is one.
> Does that mean for two tables, one 1000 rows another 2000 rows, it's a
> total of 1000 X 2000 rows scanned?
You got it :-(. (Unless we find what we're looking for before scanning
all the way through --- so M*N is the worst case, but the average case
is probably less.)
> I suppose one way to slightly reduce the number of rows would be to select
> stuff into temp tables first.
7.0 is smart enough to do that if the inner query looks complicated.
If it's just a sequential scan itself, then of course there's no win...
regards, tom lane