David Rowley <david.rowley@2ndquadrant.com> writes: > Perhaps separating out enable_nestloop so that it only disables > non-parameterised nested loops, and add another GUC for parameterised > nested loops would be a good thing to do. Likely setting enable_nestloop to > off in production would be a slightly easier decision to make, if that was > the case. > It looks pretty simple to do this, so I hacked it up, and attached it here. > There's no doc changes and I'm not that interested in fighting for this > change, it's more just an idea for consideration.
I'm not terribly excited by this idea either. If making such a change actually makes things better for someone consistently, I'd argue that the problem is a mistaken cost estimate elsewhere, and we'd be better off to find and fix the real problem. (There have already been discussions of only believing single-row rowcount estimates when they're provably true, which might help if we can figure out how to do it cheaply enough.)
Actually, it's not very hard to hit a bad underestimate at all. All you need is a join on two columns which are co-related. Since PostgreSQL multiplies the estimated selectivities the row count is going to come out too low. This also tricks the planner into thinking that this is a good join to perform early, since (it thinks that) it does not produce many rows at all. You only need 1 more join to occur after that to choose a nested loop join mistakenly to hit the issue.
I also think that the attitude that we can *always* fix the costs and estimates is not the right one. The planner is never going to get it right 100% of the time. If we ever think we can build such a planner then someone needs to come along and direct us back into the real world.
