Re: experiments in query optimization

От: Faheem Mitha
Тема: Re: experiments in query optimization
Дата: ,
Msg-id: alpine.DEB.2.00.1003302151030.13883@orwell.homelinux.org
(см: обсуждение, исходный текст)
Ответ на: Re: experiments in query optimization  ("Kevin Grittner")
Ответы: Re: experiments in query optimization  (Robert Haas)
Re: experiments in query optimization  (Matthew Wakeling)
Список: pgsql-performance

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

experiments in query optimization  (Faheem Mitha, )
 Re: experiments in query optimization  (Robert Haas, )
  Re: experiments in query optimization  (Faheem Mitha, )
   Re: experiments in query optimization  (Robert Haas, )
    Re: experiments in query optimization  (Faheem Mitha, )
   Re: experiments in query optimization  ("Kevin Grittner", )
    Re: experiments in query optimization  (Faheem Mitha, )
     Re: experiments in query optimization  (Robert Haas, )
      Re: experiments in query optimization  (Faheem Mitha, )
       Re: experiments in query optimization  (Robert Haas, )
       Re: experiments in query optimization  (Faheem Mitha, )
        Re: experiments in query optimization  (Eliot Gable, )
         Re: experiments in query optimization  (Faheem Mitha, )
          Re: experiments in query optimization  (Robert Haas, )
           Re: experiments in query optimization  (Faheem Mitha, )
            Re: experiments in query optimization  (Eliot Gable, )
             Re: experiments in query optimization  (Faheem Mitha, )
     Re: experiments in query optimization  (Matthew Wakeling, )
      Re: experiments in query optimization  (Faheem Mitha, )
    Re: experiments in query optimization  (Faheem Mitha, )


On Tue, 30 Mar 2010, Kevin Grittner wrote:

> Faheem Mitha <> wrote:
>
>>> If you're concerned about memory usage, try reducing work_mem;
>>> you've probably got it set to something huge.
>>
>> work_mem = 1 GB (see diag.{tex/pdf}).
>>
>> The point isn't that I'm using so much memory. Again, my question
>> is, why are these changes affecting memory usage so drastically?
>
> Because the planner looks at a very wide variety of plans, some of
> which may use many allocations of work_mem size, and some of which
> don't.  The costs are compared and the lowest cost one is chosen. If
> you are close to the "tipping point" then even a very small change
> might affect which is chosen.  It pays to keep the work_mem setting
> sane so that unexpected plan changes don't cause problems.

Sure, but define sane setting, please. I guess part of the point is that
I'm trying to keep memory low, and it seems this is not part of the
planner's priorities. That it, it does not take memory usage into
consideration when choosing a plan. If that it wrong, let me know, but
that is my understanding.

> Look at the plans and their costs to get a feel for what's being
> chosen and why.  Although it's a very bad idea to use these in
> production, you can often shift the plan to something you *think*
> would be better using the enable_* settings, to see what the planner
> thinks such a plan will cost and where it thinks the cost would be;
> that can help in tuning the settings.

Right. You mean to close off certain options to the planner using 'Planner
Method Configuration'. I suppose one can also use 'Planner Cost Constants'
to alter plan behaviour. I haven't tried changing these.

>>> You might need to create some indices, too.
>>
>> Ok. To what purpose? This query picks up everything from the
>> tables and the planner does table scans, so conventional wisdom
>> and indeed my experience, says that indexes are not going to be so
>> useful.
>
> There are situations where scanning the entire table to build up a
> hash table is more expensive than using an index.  Why not test it?

Certainly, but I don't know what you and Robert have in mind, and I'm not
experienced enough to make an educated guess. I'm open to specific
suggestions.

                                                          Regards, Faheem.


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

От: Robert Haas
Дата:
Сообщение: Re: experiments in query optimization
От: Brian Cox
Дата:
Сообщение: query has huge variance in execution times