On Fri, Aug 12, 2011 at 4:33 AM, Simon Riggs <si...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
