Re: Planner hints in Postgresql

On Mon, Mar 17, 2014 at 10:58 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> * Atri Sharma (atri(dot)jiit(at)gmail(dot)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.

Regards,

Atri
--
Regards,

Atri
*l'apprenant*