On Thu, Sep 29, 2022 at 7:32 AM Benjamin Coutu <ben.co...@zeyos.com> wrote: > Also, we can expand the multiplier whenever we fall back to using the default > cardinality constant as surely all bets are off at that point - we should > definitely treat nested loop joins as out of favor in this instance and that > could easily be incorporated by simply increasing the conviction-mutliplier. > > What are your thoughts on this simple idea - is it perhaps too simple?
Offhand I'd say it's more likely to be too complicated. Without meaning to sound glib, the first question that occurs to me is "will we also need a conviction multiplier conviction multiplier?". Anything like that is going to have unintended consequences that might very well be much worse than the problem that you set out to solve. Personally I still like the idea of just avoiding unparameterized nested loop joins altogether when an "equivalent" hash join plan is available. I think of it as preferring the hash join plan because it will have virtually the same performance characteristics when you have a good cardinality estimate (probably very often), but far better performance characteristics when you don't. We can perhaps be approximately 100% sure that something like that will be true in all cases, no matter the details. That seems like a very different concept to what you've proposed. -- Peter Geoghegan
