Re: Planner hints in Postgresql

On Monday, March 17, 2014, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:

>
>
>
> On Mon, Mar 17, 2014 at 10:58 PM, Stephen Frost <sfrost(at)snowman(dot)net<javascript:_e(%7B%7D,'cvml','sfrost(at)snowman(dot)net');>
> > wrote:
>
>> * Atri Sharma (atri(dot)jiit(at)gmail(dot)com<javascript:_e(%7B%7D,'cvml','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.
>

I'm not opposed to planner hints (or plan mandates), but also not
optimistic they will ever get implemented, much less accepted. But if they
were, I don't see a use for such fudge factors. By mandating a plan, I am
already asserting I know more than the optimizer does. Maybe I am right,
maybe I am wrong, but either way I have taken it out of the optimizer's
hands and would not welcome it snatching control back.

If it is too deranged for me to trust, why would it not become somewhat
more deranged and so decide to ignore my hints? The only setting for such
a factor I would ever see myself using was the minimum, or the maximum.

The feature I would like in such hints, if they are to exist, is to set a
version to which they apply. Often a fix is made very quickly after the
problem is pointed out, but it could take well over a year for the fix to
see production if it is not backpatched and it lands at the wrong part of
the release cycle.

Cheers,

Jeff

>