Re: Am I really stupid??? - Mailing list pgsql-general

From Tom Lane
Subject Re: Am I really stupid???
Date
Msg-id 26489.958700454@sss.pgh.pa.us
Whole thread Raw
In response to Re: Am I really stupid???  (Lincoln Yeoh <lylyeoh@mecomb.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Lincoln Yeoh
Date:
Subject: Re: Am I really stupid???
Next
From: Lincoln Yeoh
Date:
Subject: Re: PostgreSQL cleartext passwords