Re: our buffer replacement strategy is kind of lame

On Fri, Aug 12, 2011 at 5:05 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> rhaas=# select usagecount, sum(1) from pg_buffercache group by 1 order by 1;
> usagecount |  sum
> ------------+-------
>          1 |   136
>          2 |    12
>          3 |    72
>          4 |     7
>          5 | 13755
>            | 37218
> (6 rows)
>
> Only 96 of the 14286 buffers in sample_data are in shared_buffers,
> despite the fact that we have 37,218 *completely unused* buffers lying
> around.  That sucks, because it means that the sample query did a
> whole lot of buffer eviction that was completely needless.  The ring
> buffer is there to prevent trashing the shared buffer arena, but here
> it would have been fine to trash the arena: there wasn't anything
> there we would have minded losing (or, indeed, anything at all).

You're missing an important point. The SeqScan is measurably faster
when using the ring buffer because of the effects of L2 cacheing on
the buffers.

Also, your logic is a little off, since you're doing the scan on an
otherwise quiet machine soon after startup and there are some
completely unused buffers. That isn't the typical state of the buffer
cache and so spoiling the cache is not acceptable in the general case.
The above case doesn't suck because it carefully reserved parts of
shared buffers for other users when spoiling the cache would be of no
benefit to the user, so it worked great from my perspective.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services