Postgres Pro
Downloads
Home   >   mailing lists

Re: Planner hints in Postgresql - Mailing list pgsql-hackers

From Jeff Janes
Subject Re: Planner hints in Postgresql
Date
Msg-id CAMkU=1zfeabmNmGBL6zuT73zo4VstRVbzTKYVkQQq87KNGCKPA@mail.gmail.com
Whole thread Raw
In response to Re: Planner hints in Postgresql  (Atri Sharma <atri.jiit@gmail.com>)
List pgsql-hackers
Tree view
On Monday, March 17, 2014, Atri Sharma <atri.jiit@gmail.com> wrote:



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.

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

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: pg_archivecleanup bug
Next
From: Jeff Janes
Date:
Subject: Re: Planner hints in Postgresql