Re: generalizing the planner knobs

wouldn't it be more flexible to define a multiplicator or some sort
of bool flag on a per object level?
oracle hints are a total overkill and i agree with tom that usually
people will abuse this feature.
if we had a per object flag the actual planner hint can be decoupled
from the actual query (i don't think putting a hint inside a query is
the most clever thing).
changing a flag would be as simple as running UPDATE on some system
table.
this should not be too intrusive as well.

best regards,

hans

On Dec 1, 2005, at 7:45 PM, Jonah H. Harris wrote:

> Tom,
>
> Don't get me wrong, I agree with you completely. I would rather
> put effort into enhancing the planner than in developing work-
> arounds. In 99% of all cases the planner works correctly, but I
> know people who actually have to disable planning options
> (mergejoin) in production applications because they get bad plans.
> The "bad" plans are not really bad in terms of what the planner
> knows about the query, just in areas where the planner doesn't look
> at other things.
>
> I also agree that a significant amount of work would be required to
> add run-time hints which would be better spent enhancing the system
> as a whole. My only suggestion was that it would be better than
> Part 1 of Neil's statement. Somehow I missed the end mention of
> multipliers which I agree requires less effort.
>
>
>
> On 12/1/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
> > In the last couple weeks I too have been thinking about planner
> hints.
> > Assuming I have read your post correctly, the issue I see with
> this idea is
> > that, in most cases, there won't be much of a difference between
> adding an
> > arbitrary cost value to each type of node and disabling it
> completely.
> > Also, by fiddling with an arbitrary cost the user may introduce a
> lot of
> > variation into the planner which may actually result in worse
> query plans.
>
> Which is pretty much exactly the problem with "planner hints", too.
> I've resisted that suggestion in the past and will continue to do so,
> because hints are accidents waiting to happen. Even if the hint is
> right
> today for your current Postgres version and current data distribution,
> it's likely not to be right further down the road --- but once the
> hint
> is embedded in your application, how often are you going to revisit
> it?
> As an example, a hint forcing the planner to use an indexscan with a
> particular index might have been a great idea in PG 8.0 and a lousy
> idea
> in 8.1, because it would prevent substitution of a possibly-far-better
> bitmap indexscan.
>
> The enable_foo switches are debug aids, not something you are expected
> to fool with for production purposes, and the same would be true of
> Neil's suggested multipliers. While I don't feel any strong need for
> variable multipliers, they'd be a small enough incremental amount of
> work that the suggestion doesn't require a lot of supporting argument.
> Adding a planner hint facility would be several orders of magnitude
> more work, and it would be taking the system in a design direction
> that
> I think is fundamentally misguided.
>
> regards, tom lane
>