Re: Simple join optimized badly?

От: Jim C. Nasby
Тема: Re: Simple join optimized badly?
Дата: ,
Msg-id: 20061009211827.GL72517@nasby.net
(см: обсуждение, исходный текст)
Ответ на: Re: Simple join optimized badly?  (Chris Browne)
Ответы: Re: Simple join optimized badly?  (Tom Lane)
Re: Simple join optimized badly?  (Mark Kirkwood)
Список: pgsql-performance

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

Simple join optimized badly?  ("Craig A. James", )
 Re: Simple join optimized badly?  (Tom Lane, )
  Re: Simple join optimized badly?  ("Denis Lussier", )
   Re: Simple join optimized badly?  (Jim Nasby, )
   Re: Simple join optimized badly?  (Josh Berkus, )
    Re: Simple join optimized badly?  ("Craig A. James", )
     Re: Simple join optimized badly?  (Mark Kirkwood, )
      Re: Simple join optimized badly?  ("Craig A. James", )
       Re: Simple join optimized badly?  (Mark Kirkwood, )
        Re: Simple join optimized badly?  (Tom Lane, )
         Re: Simple join optimized badly?  (Josh Berkus, )
          Re: Simple join optimized badly?  (Tom Lane, )
    Re: Simple join optimized badly?  (Tom Lane, )
    Re: Simple join optimized badly?  (Scott Marlowe, )
 Re: Simple join optimized badly?  (Bruce Momjian, )
  Re: Simple join optimized badly?  ("Craig A. James", )
 Re: Simple join optimized badly?  (Chris Browne, )
 Re: Simple join optimized badly?  (Chris Browne, )
  Re: Simple join optimized badly?  ("Jim C. Nasby", )
   Re: Simple join optimized badly?  (Tom Lane, )
   Re: Simple join optimized badly?  (Mark Kirkwood, )
    Re: Simple join optimized badly?  (Mark Kirkwood, )
 Re: Simple join optimized badly?  (Tobias Brox, )
  Re: Simple join optimized badly?  ("Jim C. Nasby", )
   Re: Simple join optimized badly?  ("Joshua D. Drake", )
    Re: Simple join optimized badly?  ("Jim C. Nasby", )
     Re: Simple join optimized badly?  ("Joshua D. Drake", )
   Re: Simple join optimized badly?  (Tom Lane, )
    Re: Simple join optimized badly?  ("Jim C. Nasby", )
     Re: Simple join optimized badly?  (Tom Lane, )
      Re: Simple join optimized badly?  ("Jim C. Nasby", )
       Re: Simple join optimized badly?  (Josh Berkus, )
        Re: Simple join optimized badly?  ("Jim C. Nasby", )
  Re: Simple join optimized badly?  (Bruno Wolff III, )
 Re: Simple join optimized badly?  (Brian Herlihy, )
  Re: Simple join optimized badly?  ("Craig A. James", )
   Re: Simple join optimized badly?  ("Joshua D. Drake", )
    Re: Simple join optimized badly?  ("Jim C. Nasby", )
     Re: Simple join optimized badly?  ("Steinar H. Gunderson", )
      Re: Simple join optimized badly?  ("Joshua D. Drake", )
     Re: Simple join optimized badly?  ("Joshua D. Drake", )
      Re: Simple join optimized badly?  (Brian Herlihy, )
       Re: Simple join optimized badly?  (Tom Lane, )
        Re: Simple join optimized badly?  (Brian Herlihy, )
        Re: Simple join optimized badly?  ("Bucky Jordan", )
         Re: Simple join optimized badly?  (Heikki Linnakangas, )
          Re: Simple join optimized badly?  (Bruce Momjian, )
         Collect stats during seqscan (was: Simple join optimized badly?)  ("Jim C. Nasby", )
        Re: Simple join optimized badly?  (Mark Lewis, )

On Mon, Oct 09, 2006 at 06:07:29PM +0000, Chris Browne wrote:
>  ("Craig A. James") writes:
> > Mark Kirkwood wrote:
> >>> The result?  I can't use my function in any WHERE clause that
> >>> involves any other conditions or joins.  Only by itself.  PG will
> >>> occasionally decide to use my function as a filter instead of doing
> >>> the join or the other WHERE conditions first, and I'm dead.
> >> this is an argument for cost-for-functions rather than hints AFAICS.
> >
> > Perhaps you scanned past what I wrote a couple paragraphs farther
> > down.  I'm going to repeat it because it's the KEY POINT I'm trying
> > to make:
> >
> > Craig James wrote:
> >> Now you might argue that function-cost needs to be added to the
> >> optimizer's arsenal of tricks.  And I'd agree with you: That WOULD
> >> be a better solution than hints.  But I need my problem solved
> >> TODAY, not next year.  Hints can help solve problems NOW that can be
> >> brought to the PG team's attention later, and in the mean time let
> >> me get my application to work.
>
> Unfortunately, that "hint language" also needs to mandate a temporal
> awareness of when hints were introduced so that it doesn't worsen
> things down the road.
>
> e.g. - Suppose you upgrade to 8.4, where the query optimizer becomes
> smart enough (perhaps combined with entirely new kinds of scan
> strategies) to make certain of your hints obsolete and/or downright
> wrong.  Those hints (well, *some* of them) ought to be ignored, right?

Great, then you pull the hints back out of the application. They're a
last resort anyway; if you have more than a handful of them in your code
you really need to look at what you're doing.

> The trouble is that the "hint language" will be painfully large and
> complex.  Its likely-nonstandard interaction with SQL will make query
> parsing worse.
>
> All we really have, at this point, is a vague desire for a "hint
> language," as opposed to any clear direction as to what it should look
> like, and how it needs to interact with other system components.
> That's not nearly enough; there needs to be a clear design.

I can agree to that, but we'll never get any progress so long as every
time hints are brought up the response is that they're evil and should
never be in the database. I'll also say that a very simple hinting
language (ie: allowing you to specify access method for a table, and
join methods) would go a huge way towards enabling app developers to get
stuff done now while waiting for all these magical optimizer
improvements that have been talked about for years.
--
Jim Nasby                                            
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


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

От: Brian Herlihy
Дата:
Сообщение: Re: Simple join optimized badly?
От: "Craig A. James"
Дата:
Сообщение: Re: Simple join optimized badly?