Re: our buffer replacement strategy is kind of lame

On Fri, Aug 12, 2011 at 8:35 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Fri, Aug 12, 2011 at 1:26 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>  But it will be
>> a loser to apply the optimization to data sets that would otherwise
>> have fit in shared_buffers.
>
> Spoiling the cache is a bad plan, even if it makes the current query faster.
>
> I think we should make the optimisation stronger still and use
> FADV_DONT_NEED on blocks that fall from the ring buffer. Spoiling the
> OS cache is a problem as well.

That would probably be better for really big tables, but it will be
worse for those where the entire table fits in the OS cache.

Caching spoiling means you're putting things into the cache which
won't still be there the next time they're needed. If the data in
question fits in cache (and I don't think we can regard that as
uncommon, particularly for the OS cache, which can be half a terabyte)
then you don't want the anti-spoiling logic to kick in.

One thing we could consider for large sequential scans is doing
POSIX_FADV_SEQUENTIAL before beginning the scan (and maybe one more
time if the scan wraps). That's basically throwing the problem of
whether or not to go LRU or MRU back on the OS, but the OS may well
have a better idea whether there's enough cache available to hold the
whole than we do.

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