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.


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:

Reply via email to