Re: our buffer replacement strategy is kind of lame

On Fri, Aug 12, 2011 at 4:33 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> 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.

I hadn't thought of that, but I think that's only true if the relation
won't fit in shared_buffers (or whatever portion of shared_buffers is
reasonably available, given the other activity on the machine). In
this particular case, it's almost 20% faster if the relation is all in
shared_buffers; I tested it. I think what's going on here is that
initscan() has a heuristic that tries to use a BufferAccessStrategy if
the relation is larger than 1/4 of shared_buffers. That's probably a
pretty good heuristic in general, but in this case I have a relation
which just so happens to be 27.9% of shared_buffers but will still
fit. As you say below, that may not be typical in real life, although
there are probably data warehousing systems where it's normal to have
only one big query running at a time.

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

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company