Re: WIP patch for parameterized inner paths

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> ... In an ideal world, I'd like the amount of effort we spend
> planning to be somehow tied to the savings we can expect to get, and
> deploy optimizations like this only in cases where we have a
> reasonable expectation of that effort being repaid.

BTW, so far as that goes: the only way I know to significantly cut the
planner's join-planning resource consumption in any run-time-tunable
fashion is to restrict it to planning subproblems separately, as for
instance via from_collapse_limit/join_collapse_limit. Now, whatever the
merits of those specific heuristics, the killer issue is that maybe you
really needed a join order different from what the subproblem division
entails. I believe that this patch can help with that. If the issue
is that you really don't want to form the entire result of a specific
subproblem subquery, that can be dealt with by treating the subquery as
a parameterizable path, such that it can be on the inside of a nestloop
with whatever other relation is supplying the parameter.

Or in other words, the reason from_collapse_limit/join_collapse_limit
sometimes lead to bad plans is really that they represent artificial
join order restrictions. So I think this patch ought to be able to
alleviate the worst cases of that. Or at least it did a few hours ago.
What we'll probably want to do is tweak the path-formation heuristic
you suggested so that joins to relations outside the current subproblem
are treated like outer joins and allowed to form parameterized paths.
Anyway this is the sort of thing that I hope to investigate after the
basic patch is in.

regards, tom lane