Re: Simple join optimized badly?

От: Tom Lane
Тема: Re: Simple join optimized badly?
Дата: ,
Msg-id: 19817.1160369035@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Simple join optimized badly?  (Mark Kirkwood)
Ответы: Re: Simple join optimized badly?  (Josh Berkus)
Список: 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, )

Mark Kirkwood <> writes:
> True enough - but (aside from the fact that hints might take just as
> long to get into the development tree as cost-for-functions might take
> to write and put in...) there is a nasty side effect to adding hints -
> most of the raw material for optimizer improvement disappears (and hence
> optimizer improvement stalls)- why? simply that everyone then hints
> everything - welcome to the mess that Oracle are in (and seem to be
> trying to get out of recently)!

And *that* is exactly the key point here.  Sure, if we had unlimited
manpower we could afford to throw some at developing a hint language
that would be usable and not too likely to break at every PG revision.
But we do not have unlimited manpower.  My opinion is that spending
our development effort on hints will have a poor yield on investment
compared to spending similar effort on making the planner smarter.

Josh's post points out some reasons why it's not that easy to get
long-term benefits from hints --- you could possibly address some of
those problems, but a hint language that responds to those criticisms
won't be trivial to design, implement, or maintain.  See (many) past
discussions for reasons why not.

            regards, tom lane


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

От: Tom Lane
Дата:
Сообщение: Re: Simple join optimized badly?
От: "Merlin Moncure"
Дата:
Сообщение: Re: Performance Optimization for Dummies 2 - the SQL