Re: [HACKERS] Hints proposal

On Thu, Oct 12, 2006 at 01:58:22PM -0700, Josh Berkus wrote:
> > Unless you've got a time machine or a team of coders in your back
> > pocket, I don't see how the planner will suddenly become perfect in
> > 8.4...
>
> Since you're not a core code contributor, I really don't see why you
> continue to claim that query hints are going to be easier to implement
> than relation-level statistics modification. You think it's easier, but
> the people who actually work on the planner don't believe that it is.

Well, that's not what I said (my point being that until the planner and
stats are perfect you need a way to over-ride them)... but I've also
never said hints would be faster or easier than stats modification (I
said I hope they would). But we'll never know which will be faster or
easier until there's actually a proposal for improving the stats.

> > We've been seeing the same kinds of problems that are very difficult (or
> > impossible) to fix cropping up for literally years... it'd be really
> > good to at least be able to force the planner to do the sane thing even
> > if we don't have the manpower to fix it right now...
>
> As I've said to other people on this thread, you keep making the incorrect
> assumption that Oracle-style query hints are the only possible way of
> manual nuts-and-bolts query tuning. They are not.

No, I've never said that. What I've said is a) I doubt that any system
will always be correct for every query, meaning you need to be able to
change things on a per-query basis, and b) I'm hoping that simple hints
will be easy enough to implement that they can go into 8.3.

I completely agree that it's much better *in the long run* to improve
the planner and the statistics system so that we don't need hints. But
there's been no plan put forward for how to do that, which means we also
have no idea when some of these problems will be resolved. If someone
comes up with a plan for that, then we can actually look at which options
are better and how soon we can get fixes for these problems in place.

Unfortunately, this problem is difficult enough that I suspect it could
take a long time just to come up with an idea of how to fix these
problems, which means that without some way to override the planner our
users are stuck in the same place for the foreseeable future. If that
turns out to be the case, then I think we should implement per-query
hints now so that users can handle bad plans while we focus on how to
improve the stats and planner so that in the future hints will become
pointless.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)