Re: generalizing the planner knobs

Hey Neil,

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.

While Tom's done a great job with the planner, there are certain cases where
a user knows exactly what type of join or index they want to use for a
query. In that case I'd favor run-time hints from the user similar to
Oracle. I've read about seven papers on query optimization and planning in
the last few weeks and have a lot of ideas... I'm just not sure when I may
get time to work on them :(

-Jonah

On 12/1/05, Neil Conway <neilc(at)samurai(dot)com> wrote:
>
> There are currently some rather crude knobs for persuading the planner
> to favour certain kinds of query plans: the enable_XXX GUC variables.
> Several people have asked for a more flexible way to give hints to the
> planner. I'm not interested in implementing fully-general planner hints
> at the moment, but ISTM that a simple improvement to what we have now
> would allow for a wider range of planner hints with only minor changes:
> we could replace the enable_XXX variables with a set of variables that
> would add an arbitrary constant to the estimated cost of each type of
> query node. (Alternatively, an arbitrary multiplier could be specified;
> I'm not sure which would be better.)
>
> This would also be useful when diagnosing bad query plans: for example,
> setting enable_seqscan=false often causes the planner to disregard the
> use of *any* sequential scan, anywhere in the plan. The ability to
> slightly bump up the cost of particular operations would allow more
> alternative plans to be examined.
>
> On the other hand, the whole mechanism is still a hack. It also means
> that applications using this will be more dependent on the actual
> costing values produced by the planner, which is not good. However, if
> you're in the sort of desperate straights where this sort of hackery is
> required, perhaps that's acceptable.
>
> Comments?
>
> -Neil
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>