> 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).
