Re: Recursive optimization of IN subqueries

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.

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