Re: generalizing the planner knobs - Mailing list pgsql-hackers

From Hans-Juergen Schoenig
Subject Re: generalizing the planner knobs
Date
Msg-id E365094A-266F-4C66-888D-22F3A6D01276@cybertec.at
Whole thread Raw
In response to Re: generalizing the planner knobs  ("Jonah H. Harris" <jonah.harris@gmail.com>)
List pgsql-hackers
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@sss.pgh.pa.us> wrote:
"Jonah H. Harris" <jonah.harris@gmail.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


pgsql-hackers by date:

Previous
From: Philip Warner
Date:
Subject: Re: Optimizer oddness, possibly compounded in 8.1
Next
From: Simon Riggs
Date:
Subject: Striping CLOG and Subtrans