Re: Recursive optimization of IN subqueries

Tom Lane wrote:

>Dennis Haney <davh(at)diku(dot)dk> writes:
>
>
>>As far as I can tell, the pull_up_IN_clauses does not optimize
>>recursively. Am I totally misguided here?
>>
>>
>
>Yes. The subquery is not being physically folded into the outer query
>(so the comment about "pulling up" may be a poor choice of words).
>It will still get planned separately by a recursive call to
>subquery_planner, and any internal INs will get fixed at that time.
>
>It is possible and even rather likely that the subsequent run of
>pull_up_subqueries will flatten the subquery into the outer query,
>and if so its internal INs are fixed during pull_up_subqueries.
>But doing it here would be redundant.
>
>
I think I figured it out now, after looking at it for hours...

I saw it as though convert_IN_to_join rewrote the query from

select a.* from tenk1 a where a.unique1 in
(select c.thousand from tenk1 c where c.hundred = 99);

to

select a.* from tenk1 a, tenk1 c where a.unique1 = c.thousand AND
c.hundred = 99;

But after looking at it, I've reached the conclusion that the rewrite is
to this instead:

select a.* from tenk1 a, (select d.thousand from tenk1 d where
d.hundred = 99) as c where a.unique1 = c.thousand;

except the subselect is added as a range table entry instead of a
subselect in the from-list (not that I understand this particular part,
do you mind explaining?).

Or am I still totally lost?

--
Dennis