On 2014-05-06 17:43:45 +0100, Simon Riggs wrote: > On 6 May 2014 15:17, Andres Freund <and...@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.
It is. > 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. 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. Especially on bigger machines shared_buffers can't be set big enough to actually use all the machine's memory. It's not uncommon to have 4GB shared buffers on a machine with 512GB RAM... It'd be absolutely disastrous to set effective_cache_size to 1GB for an analytics workload. > 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. More often than not indexes are smaller than the table size, so this argument doesn't seem to make much sense. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
