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

On Tue, May 6, 2014 at 9:55 AM, Andres Freund <andres(at)2ndquadrant(dot)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