Re: [PERFORM] Hints proposal

От: Jim C. Nasby
Тема: Re: [PERFORM] Hints proposal
Дата: ,
Msg-id: 20061013161614.GT28647@nasby.net
(см: обсуждение, исходный текст)
Ответ на: Re: [PERFORM] Hints proposal  (Josh Berkus)
Ответы: Re: [PERFORM] Hints proposal  (Tom Lane)
Re: [PERFORM] Hints proposal  ("Bucky Jordan")
Re: [PERFORM] Hints proposal  (Josh Berkus)
Список: pgsql-hackers

Скрыть дерево обсуждения

Re: [PERFORM] Hints proposal  (Tom Lane, )
 Re: [PERFORM] Hints proposal  ("Merlin Moncure", )
 Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
  Re: [PERFORM] Hints proposal  (Tom Lane, )
  Re: [PERFORM] Hints proposal  (Josh Berkus, )
   Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
    Re: [PERFORM] Hints proposal  (Josh Berkus, )
     Re: [PERFORM] Hints proposal  ("Bucky Jordan", )
      Re: [PERFORM] Hints proposal  (Alvaro Herrera, )
       Re: [PERFORM] Hints proposal  (Jeff Davis, )
        Re: [PERFORM] Hints proposal  ("Bucky Jordan", )
         Re: [PERFORM] Hints proposal  (Jeff Davis, )
          Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
           Re: [PERFORM] Hints proposal  ("Joshua D. Drake", )
            Re: [PERFORM] Hints proposal  (Andrew Dunstan, )
           Re: [PERFORM] Hints proposal  (David Fetter, )
           Re: [PERFORM] Hints proposal  (Jeff Davis, )
           Re: [PERFORM] Hints proposal  (Stefan Kaltenbrunner, )
     Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
      Re: [PERFORM] Hints proposal  (Tom Lane, )
      Re: [PERFORM] Hints proposal  ("Bucky Jordan", )
      Re: [PERFORM] Hints proposal  (Josh Berkus, )
       Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
        Re: [PERFORM] Hints proposal  (Alvaro Herrera, )
        Re: [PERFORM] Hints proposal  (Tom Lane, )
     Re: [PERFORM] Hints proposal  (Gregory Stark, )
      Re: [PERFORM] Hints proposal  (Robert Treat, )
   Re: [PERFORM] Hints proposal  ("Zeugswetter Andreas ADI SD", )
    Re: [PERFORM] Hints proposal  (Josh Berkus, )
  Re: [PERFORM] Hints proposal  (Andrew Sullivan, )
   Re: [PERFORM] Hints proposal  ("Merlin Moncure", )
    Re: [PERFORM] Hints proposal  (Andrew Sullivan, )
   Re: [PERFORM] Hints proposal  ("Zeugswetter Andreas ADI SD", )
    Re: [PERFORM] Hints proposal  (Andrew Sullivan, )
     Re: [PERFORM] Hints proposal  (Csaba Nagy, )
      Re: [PERFORM] Hints proposal  (Josh Berkus, )
     Re: [PERFORM] Hints proposal  ("Zeugswetter Andreas ADI SD", )
 Re: [PERFORM] Hints proposal  (Csaba Nagy, )
  Re: [PERFORM] Hints proposal  (Josh Berkus, )
   Re: [PERFORM] Hints proposal  (Csaba Nagy, )
  Re: [PERFORM] Hints proposal  (Tom Lane, )

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                                            
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


В списке pgsql-hackers по дате сообщения:

От: "Dann Corbit"
Дата:
Сообщение: Re: [GENERAL] more anti-postgresql FUD
От: Tom Lane
Дата:
Сообщение: Re: ./configure argument checking