Re: generalizing the planner knobs

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
>