On Fri, Aug 12, 2011 at 5:05 AM, Robert Haas <robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers