От: Jeff Davis
Тема: Re: Hints proposal
Дата: ,
Msg-id: 1160692860.31966.153.camel@dogma.v10.wvs
(см: обсуждение, исходный текст)
Ответ на: Re: Hints proposal  ("Jim C. Nasby")
Список: pgsql-performance

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

Hints proposal  ("Jim C. Nasby", )
 Re: Hints proposal  (Bruce Momjian, )
  Re: Hints proposal  (Heikki Linnakangas, )
   Re: Hints proposal  (Tom Lane, )
   Re: Hints proposal  ("Jim C. Nasby", )
 Re: Hints proposal  ("Joshua Marsh", )
  Re: Hints proposal  ("Bucky Jordan", )
   Re: Hints proposal  (Robert Treat, )
    Re: Hints proposal  (Tom Lane, )
     Re: Hints proposal  (Robert Treat, )
  Re: Hints proposal  ("Jim C. Nasby", )
 Re: Hints proposal  (Tom Lane, )
  Re: Hints proposal  ("Merlin Moncure", )
  Re: [HACKERS] Hints proposal  ("Jim C. Nasby", )
   Re: [HACKERS] Hints proposal  (Josh Berkus, )
    Re: [HACKERS] Hints proposal  ("Jim C. Nasby", )
     Re: [HACKERS] Hints proposal  (Josh Berkus, )
      Re: [HACKERS] Hints proposal  ("Bucky Jordan", )
       Re: [HACKERS] Hints proposal  (Alvaro Herrera, )
      Re: [HACKERS] Hints proposal  ("Jim C. Nasby", )
       Re: [HACKERS] Hints proposal  (Tom Lane, )
       Re: [HACKERS] Hints proposal  ("Bucky Jordan", )
        Re: Hints proposal  ("Craig A. James", )
         Re: Hints proposal  (, )
         Re: Hints proposal  (Mark Kirkwood, )
          Re: Hints proposal  (Csaba Nagy, )
           Re: Hints proposal  (Brian Hurt, )
            Re: Hints proposal  (Bruce Momjian, )
            Re: Hints proposal  (Shaun Thomas, )
           Re: Hints proposal  (Mark Kirkwood, )
       Re: [HACKERS] Hints proposal  (Josh Berkus, )
        Re: [HACKERS] Hints proposal  ("Jim C. Nasby", )
         Re: [HACKERS] Hints proposal  (Alvaro Herrera, )
         Re: [HACKERS] Hints proposal  (Tom Lane, )
      Re: [HACKERS] Hints proposal  (Gregory Stark, )
    Re: Hints proposal  ("Craig A. James", )
    Re: [HACKERS] Hints proposal  ("Zeugswetter Andreas ADI SD", )
  Re: Hints proposal  (Csaba Nagy, )
   Re: [HACKERS] Hints proposal  (Josh Berkus, )
    Re: [HACKERS] Hints proposal  (Csaba Nagy, )
   Re: [HACKERS] Hints proposal  (Tom Lane, )
 Re: Hints proposal  (Jeff Davis, )
  Re: Hints proposal  (Csaba Nagy, )
   Re: Hints proposal  (Jeff Davis, )
    Re: Hints proposal  (Arjen van der Meijden, )
     Re: Hints proposal  (Tom Lane, )
      Re: Hints proposal  (Jeff Davis, )
     Re: Hints proposal  (Richard Broersma Jr, )
     Re: Hints proposal  (Christopher Browne, )
  Re: Hints proposal  ("Jim C. Nasby", )
   Re: Hints proposal  (Jeff Davis, )
 Re: [HACKERS] Hints proposal  (Andrew Sullivan, )
 Re: [HACKERS] Hints proposal  (Robert Treat, )

On Thu, 2006-10-12 at 14:34 -0500, Jim C. Nasby wrote:
> On Thu, Oct 12, 2006 at 09:42:55AM -0700, Jeff Davis wrote:
> > On Thu, 2006-10-12 at 10:14 -0500, Jim C. Nasby wrote:
> > > The syntax these hints is something arbitrary. I'm borrowing Oracle's
> > > idea of embedding hints in comments, but we can use some other method if
> > > desired. Right now I'm more concerned with getting the general idea
> > > across.
> > >
> >
> > Is there any advantage to having the hints in the queries? To me that's
> > asking for trouble with no benefit at all. It would seem to me to be
> > better to have a system catalog that defined hints as something like:
> >
> > "If user A executes a query matching regex R, then coerce (or force) the
> > planner in this way."
> >
> > I'm not suggesting that we do that, but it seems better then embedding
> > the hints in the queries themselves.
>
> My experience is that on the occasions when I want to beat the planner
> into submission, it's usually a pretty complex query that's the issue,
> and that it's unlikely to have more than a handful of them in the
> application. That makes me think a regex facility would just get in the
> way, but perhaps others have much more extensive need of hinting.
>
> I also suspect that writing that regex could become a real bear.
>

Well, writing the regex is just matching criteria to apply the hint. If
you really need a quick fix, you can just write a comment with a query
id number in the query. The benefit there is that when the hint is
obsolete later (as the planner improves, or data changes
characteristics) you drop the hint and the query is planned without
interference. No application changes required.

Also, and perhaps more importantly, let's say you are trying to improve
the performance of an existing application where it's impractical to
change the query text (24/7 app, closed source, etc.). You can still
apply a hint if you're willing to write the regex. Just enable query
logging or some such to capture the query, and copy it verbatim except
for a few parameters which are unknown. Instant regex. If you have to
change the query text to apply the hint, it would be impossible in this
case.

> Having said that... I see no reason why it couldn't work... but the real
> challenge is defining the hints.

Right. The only thing I was trying to solve was the problems associated
with the hint itself embedded in the client code. I view that as a
problem that doesn't need to exist.

I'll leave it to smarter people to either improve the planner or develop
a hinting language. I don't even need hints myself, just offering a
suggestion.

Regards,
    Jeff Davis



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

От: Christopher Browne
Дата:
Сообщение: Re: Hints proposal
От: "Jim C. Nasby"
Дата:
Сообщение: Re: [HACKERS] Hints proposal