Re: large tables and simple "= constant" queries using

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@[EMAIL PROTECTED]
 http://www.gregsmith.com
Baltimore, MD

-- 
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance