Re: Recursive optimization of IN subqueries

Tom Lane wrote:

Dennis Haney <davh@diku.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