On Tue, May 6, 2014 at 9:55 AM, Andres Freund <and...@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
