Thank you both for your help. We will test your patch but we need to understand a bit more the code in order to follow your discussions. Actually, your patch helps us to find where to start in the code ;).
> The planner is never going to get it right 100% of the time. Yes, I agree. In production environnements, even if PostgreSQL chooses such a bad plan 1% of the time, it is enough to make clients angry. My goal is to eradicate this risk of choosing a nested loop in certain cases, which freezes PostgreSQL during many minutes, whereas a hash-join or something else takes only 2 seconds to complete. The performance difference is huge. I mean, even if the plan is not the best one 100% of the time, it should at least choose a "risk-free" plan, without these "bad" nested-loops. It is maybe easier said than done but we want to try. Regards, *David Grelaud* 2016-01-15 2:16 GMT+01:00 David Rowley <david.row...@2ndquadrant.com>: > On 15 January 2016 at 04:00, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> David Rowley <david.row...@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. > > FWIW TPC-H Q9 has this exact trip hazard with the partsupp table, which is > the exact reason why this patch was born: > https://commitfest.postgresql.org/7/210/ > > 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. > > -- > David Rowley http://www.2ndQuadrant.com/ > <http://www.2ndquadrant.com/> > PostgreSQL Development, 24x7 Support, Training & Services >
