On Thu, 10 Apr 2008, Bill Moran wrote:
> If you install the pg_buffercache addon, you can actually look into
> PostgreSQL's internals and see what tables are in the buffer in real
> time.
The "Inside the PostgreSQL Buffer Cache" talk I did at the recent East
conference is now on-line at
http://www.westnet.com/~gsmith/content/postgresql/
The slides explain how that information gets updated and used internally,
and the separate "sample queries" file there shows some more complicated
views I've written against pg_buffercache. Here's a sample one:
relname |buffered| buffers % | % of rel
accounts | 306 MB | 65.3 | 24.7
accounts_pkey | 160 MB | 34.1 | 93.2
This shows that 65.3% of the buffer cache is filled with the accounts
table, which is caching 24.7% of the full table. These are labeled
"relations" because there's a mix of table and index data there.
accounts_pkey is an index for example, which is why almost all of it is
staying inside the buffer cache.
The queries that use usage_count only work against 8.3, that one above
should work on older versions as well.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD