Re: Questions on query planner, join types, and work_mem

От: Kevin Grittner
Тема: Re: Questions on query planner, join types, and work_mem
Дата: ,
Msg-id: 4C592C6F02000025000341BA@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: Questions on query planner, join types, and work_mem  (Peter Hussey)
Список: pgsql-performance

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

Questions on query planner, join types, and work_mem  (Peter Hussey, )
 Re: Questions on query planner, join types, and work_mem  (Andres Freund, )
 Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
  Re: Questions on query planner, join types, and work_mem  (Alvaro Herrera, )
   Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
 Re: Questions on query planner, join types, and work_mem  (Jayadevan M, )
 Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
  Re: Questions on query planner, join types, and work_mem  (Peter Hussey, )
   Re: Questions on query planner, join types, and work_mem  (Robert Haas, )
   Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
   Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
    Re: Questions on query planner, join types, and work_mem  (Robert Haas, )
     Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
      Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
       Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
        Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
         Re: Questions on query planner, join types, and work_mem  (Greg Smith, )
          Re: Questions on query planner, join types, and work_mem  (Greg Smith, )
          Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
           Re: Questions on query planner, join types, and work_mem  (Greg Smith, )
            Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
          Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
         Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
         Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
          Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
         Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
       Re: Questions on query planner, join types, and work_mem  (Bruce Momjian, )
        Re: Questions on query planner, join types, and work_mem  (Robert Haas, )
         Re: Questions on query planner, join types, and work_mem  (Bruce Momjian, )
          Re: Questions on query planner, join types, and work_mem  (Bruce Momjian, )
   Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
 Re: Questions on query planner, join types, and work_mem  (Greg Smith, )

Peter Hussey <> wrote:

> I already had effective_cache_size set to 500MB.

That seems awfully small.  You do realize that this setting does not
cause PostgreSQL to allocate any memory; it merely advises how much
disk space is likely to be cached.  It should normally be set to the
sum of your shared_buffers setting and whatever your OS reports as
cached.  Setting it too small will discourage the optimizer from
picking plans which use indexes.

> I experimented with lowering  random_page_cost to 3 then 2.

As others have said, in a fully cached system that's still too high.
If the active portion of your database is fully cached, you should
set random_page_cost and seq_page_cost to the same value, and that
value should probably be in the range of 0.1 to 0.005.  It can get
trickier if the active portion is largely but not fully cached; we
have one server where we found, through experimentation, that we got
better plans overall with seq_page_cost = 0.3 and random_page_cost =
0.5 than any other settings we tried.

-Kevin


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

От: "Kevin Grittner"
Дата:
Сообщение: Re: Questions on query planner, join types, and work_mem
От: Hannu Krosing
Дата:
Сообщение: Re: Questions on query planner, join types, and work_mem