Re: [GENERAL] Recursive optimization of IN subqueries

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Dennis Haney <davh(at)diku(dot)dk>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [GENERAL] Recursive optimization of IN subqueries
Date: 2004-01-24 01:02:24
Message-ID: 8603.1074906144@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Dennis Haney <davh(at)diku(dot)dk> writes:
> But this limited optimization makes me wonder, why the limitation to
> optimizing '='?

In the first place, you wouldn't get any improvement anyway if the
combining operator is not '=' --- if it isn't, then merge and hash join
aren't applicable and so you're gonna end up with a nestloop anyhow,
which is no better than what the executor will do with a subselect.

In the second place, what the code is doing is dependent on an understanding
of the semantics of IN; I'm not sure it's applicable to, say,
WHERE outervar > ANY (SELECT innervar FROM ...)
and it's definitely not applicable to
WHERE outervar > ALL (SELECT innervar FROM ...)
In particular, the optimization paths that involve unique-ifying the
subselect output and then using it as the outer side of a join would
definitely not work for these sorts of things.

> And why must the lefthand of the sublink be a variable of the upper query?

Otherwise the expression isn't a join and I don't think the semantics are
the same as the code is expecting.

> Then I don't understand why it gives two different execution plans?

They look the same to me, other than that a different join rule is
needed (because after all IN is not the same thing as a straight join).

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eric B.Ridge 2004-01-24 02:44:25 Re: Touch row ?
Previous Message Andrew Sullivan 2004-01-24 00:41:11 Re: force drop of database others are accessing

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2004-01-24 01:21:24 Re: Disaster!
Previous Message Thomas Swan 2004-01-23 23:49:58 Re: [HACKERS] What's left?