Thread: RE: [HACKERS] Idea for speeding up uncorrelated subqueries

I thought that all correlated and uncorrelated sub-queries could be
rewritten as a join, simplifying the query tree.  It should be a mechanical
process which can probably be performed in the rewriter.

Somebody put me right.

MikeA

>> -----Original Message-----
>> From: Bruce Momjian [mailto:maillist@candle.pha.pa.us]
>> Sent: Friday, August 06, 1999 7:11 AM
>> To: Tom Lane
>> Cc: Vadim Mikheev; pgsql-hackers@postgreSQL.org
>> Subject: Re: [HACKERS] Idea for speeding up uncorrelated subqueries
>> 
>> 
>> > Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> > > Isn't it something that takes only a few hours to 
>> implement.  We can't
>> > > keep telling people to us EXISTS, especially because 
>> most SQL people
>> > > think correlated queries are slower that non-correlated 
>> ones.  Can we
>> > > just on-the-fly rewrite the query to use exists?
>> > 
>> > I was just about to suggest exactly that.  The "IN (subselect)"
>> > notation seems to be a lot more intuitive --- at least, people
>> > keep coming up with it --- so why not rewrite it to the EXISTS
>> > form, if we can handle that more efficiently?
>> 
>> Yes, we have the nice subselect feature.  I just hate to see it not
>> completely finished, performance-wise.
>> 
>> -- 
>>   Bruce Momjian                        |  http://www.op.net/~candle
>>   maillist@candle.pha.pa.us            |  (610) 853-3000
>>   +  If your life is a hard drive,     |  830 Blythe Avenue
>>   +  Christ can be your backup.        |  Drexel Hill, 
>> Pennsylvania 19026
>> 

"Ansley, Michael" wrote:
> 
> I thought that all correlated and uncorrelated sub-queries could be
> rewritten as a join, simplifying the query tree.  It should be a mechanical
> process which can probably be performed in the rewriter.

IN can't be rewritten as a join! Subquery may return duplicates
and join would return tuple for all of them. 

And how about WHERE x = (select max(y) from ...) ?

And even for WHERE x = (select y from ...) we have to check
that subquery returns exactly ONE row, or abort.

Vadim