From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Lincoln Yeoh <lylyeoh(at)mecomb(dot)com> |
Cc: | karl(at)debisschop(dot)net, ddd(at)genesis(dot)homeip(dot)net, pgsql-general(at)hub(dot)org |
Subject: | Re: Am I really stupid??? |
Date: | 2000-05-19 01:40:54 |
Message-ID: | 26489.958700454@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Lincoln Yeoh <lylyeoh(at)mecomb(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Lincoln Yeoh | 2000-05-19 01:49:07 | Re: PostgreSQL cleartext passwords |
Previous Message | Lincoln Yeoh | 2000-05-19 01:05:54 | Re: Am I really stupid??? |