On 6 May 2014 15:17, Andres Freund <andres@2ndquadrant.com> wrote:
>> Lets fix e_c_s at 25% of shared_buffers and remove the parameter
>> completely, just as we do with so many other performance parameters.
>
> That'd cause *massive* regression for many installations. Without
> significantly overhauling costsize.c that's really not feasible. There's
> lots of installations that use relatively small s_b settings for good
> reasons. If we fix e_c_s to 25% of s_b many queries on those won't use
> indexes anymore.
"many queries" can't be correct. 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.
I specifically picked 25% of shared_buffers because that is the point
at which sequential scans become more efficient and use the cache more
efficiently. If our cost models are correct, then switching away from
index scans shouldn't hurt at all.
Assuming we can use large tranches of memory for single queries has a
very bad effect on cache hit ratios. Encouraging such usage seems to
fall into the category of insane, from my perspective. Having it as a
default setting is bad.
-- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services