Re: experiments in query optimization

От: Kevin Grittner
Тема: Re: experiments in query optimization
Дата: ,
Msg-id: 4BB1DB9A020000250003020E@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: experiments in query optimization  (Faheem Mitha)
Ответы: Re: experiments in query optimization  (Faheem Mitha)
Re: experiments in query optimization  (Faheem Mitha)
Список: 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, )

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.

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.

>> 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?

-Kevin


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

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