Re: Recursive optimization of IN subqueries

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.

You can easily prove by experiment that multi-level flattening does
happen, for instance:

regression=# explain select * from tenk1 a where unique1 in
regression-# (select unique2 from tenk1 b where unique1 in
regression(# (select thousand from tenk1 c where hundred = 99));
                                               QUERY PLAN

--------------------------------------------------------------------------------
------------------------
 Nested Loop  (cost=411.66..471.82 rows=10 width=244)
   ->  HashAggregate  (cost=411.66..411.66 rows=10 width=4)
         ->  Nested Loop  (cost=351.47..411.63 rows=10 width=4)
               ->  HashAggregate  (cost=351.47..351.47 rows=10 width=4)
                     ->  Index Scan using tenk1_hundred on tenk1 c  (cost=0.00..351.23 rows=99 width=4)
                           Index Cond: (hundred = 99)
               ->  Index Scan using tenk1_unique1 on tenk1 b  (cost=0.00..6.00 rows=1 width=8)
                     Index Cond: (b.unique1 = "outer".thousand)
   ->  Index Scan using tenk1_unique1 on tenk1 a  (cost=0.00..6.00 rows=1 width=244)
         Index Cond: (a.unique1 = "outer".unique2)
(10 rows)


            regards, tom lane