Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers
Дата
Msg-id CAMkU=1zA9cLoq2=C_B3XhtDSrXq3JOw-HTNJPdTP8Tw6k19tZg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers  (Andres Freund <andres@2ndquadrant.com>)
Список pgsql-hackers
Дерево обсуждения
On Tue, May 6, 2014 at 9:55 AM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2014-05-06 17:43:45 +0100, Simon Riggs wrote:
 
> All this changes is the cost of
> IndexScans that would use more than 25% of shared_buffers worth of
> data. Hopefully not many of those in your workload. Changing the cost
> doesn't necessarily prevent index scans either. And if there are many
> of those in your workload AND you run more than one at same time, then
> the larger setting will work against you. So the benefit window for
> such a high setting is slim, at best.

Not only do you need to run more than one at a time, but they also must use mostly disjoint sets of data, in order for the larger estimate to be bad.
 

Why? There's many workloads where indexes are larger than shared buffers
but fit into the operating system's cache. And that's precisely what
effective_cache_size is about.

It is more about the size of the table referenced by the index, rather than the size of the index.  The point is that doing a large index scan might lead you to visit the same table blocks repeatedly within quick succession.  (If a small index scan is on the inner side of a nested loop, then you might access the same index leaf blocks and the same table blocks repeatedly--that is why is only mostly about the table size, rather than exclusively).
 
Cheers,

Jeff

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Andres Freund
Дата:
Сообщение: Re: Wanted: jsonb on-disk representation documentation
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers