Re: disfavoring unparameterized nested loops

> Agreed, but dealing with uncertainty in those numbers is an enormous
> task if you want to do it right. "Doing it right", IMV, would start
> out by extending all the selectivity estimation functions to include
> error bars; then we could have error bars on rowcount estimates and
> then costs; then we could start adding policies about avoiding plans
> with too large a possible upper-bound cost. Trying to add such
> policy with no data to go on is not going to work well.

Error bars would be fantastic, no question. But that would make things very complex.
A lot of judgment calls would be necessary for the policy behind upper-bound pruning, picking up on Peter's comment about "conviction multiplier of conviction multiplier" ;)
Also, the math in deriving those bounds based on the stats and how they propagate up the join tree doesn't seem trivial either.

> I think Peter's point is that a quick-n-dirty patch is likely to make
> as many cases worse as it makes better. That's certainly my opinion
> about the topic.

As in my reply to Peter, I think the join level/depth metric is a simple but principled way of dealing with it, given the referenced research.
In the first step, we'd use this merely to be more risk-averse towards nested loop joins as we climb up the join tree - we are not fiddling with the cost model itself, nor the join ordering, just when it comes to considering that particular join algorithm. Later this could be expanded to be more broadly scoped.

Please not give up on a simple way to reap most of the fruits just yet.