Re: Simple join optimized badly?

От: Mark Kirkwood
Тема: Re: Simple join optimized badly?
Дата: ,
Msg-id: 4529B6D0.2010206@paradise.net.nz
(см: обсуждение, исходный текст)
Ответ на: Re: Simple join optimized badly?  ("Craig A. James")
Ответы: Re: Simple join optimized badly?  ("Craig A. James")
Список: 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, )

Craig A. James wrote:
>
>
> My example, discussed previously in this forum, is a classic.  I have a
> VERY expensive function (it's in the class of NP-complete problems, so
> there is no faster way to do it).  There is no circumstance when my
> function should be used as a filter, and no circumstance when it should
> be done before a join.  But PG has no way of knowing the cost of a
> function, and so the optimizer assigns the same cost to every function.
> Big disaster.
>
> 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.

It seems to me that if (in addition to the function cost) we come up
with some efficient way of recording cross column statistics we would be
well on the way to silencing *most* of the demands for hints.

We would still be left with some of the really difficult problems - a
metric for "locally correlated" column distributions and a reliable
statistical algorithm for most common value sampling (or a different way
of approaching this). These sound like interesting computer science or
mathematics thesis topics, maybe we could try (again?) to get some
interest at that level?

Cheers

Mark




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

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