On Mon, Mar 17, 2014 at 10:58 PM, Stephen Frost <sfrost@snowman.net> wrote:
* Atri Sharma (atri.jiit@gmail.com) wrote: > Isnt using a user given value for selectivity a pretty risky situation as > it can horribly screw up the plan selection? > > Why not allow the user to specify an alternate plan and have the planner
Uh, you're worried about the user given us a garbage selectivity, but they're going to get a full-blown plan perfect?
I never said that the user plan would be perfect. The entire point of planner hints is based on the assumption that the user knows more about the data than the planner does hence the user's ideas about the plan should be given a preference. Garbage selectivity can screw up the cost estimation of *all* our possible plans and we could end up preferring a sequential scan over an index only scan for e.g. I am trying to think of ways that give some preference to a user plan but do not interfere with the cost estimation of our other potential plans.
What exactly would such a "preference" look like? A cost modifier? We'd almost certainly have to make that into a GUC or a value passed in as part of the query, with a high likelihood of users figuring out how to use it to say "use my plan forever and always"..
A factor that we experimentally determine by which we decrease the cost of the user specified plan so that it gets a higher preference in the plan evaluation.
Of course, this is not a nice hack. Specifically after our discussion on IRC the other day, I am against planner hints, but if we are just discussing how it could be done, I could think of some ways which I listed.