From: Greg Smith <gsmith@gregsmith.com>
To: Bill Moran <wmoran@collaborativefusion.com>
Cc: John Beaver <john.e.beaver@gmail.com>
Cc: Gaetano Mendola <mendola@gmail.com>
Cc: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: Re: large tables and simple "= constant" queries using indexes
Date: Thu, 10 Apr 2008 14:47:59 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0804101437030.15531@westnet.com> (raw)
In-Reply-To: <20080410130854.f3a3f110.wmoran@collaborativefusion.com>
References: <47FD2DF3.80500@gmail.com>
<47FD3350.4060504@tweakers.net>
<47FD4212.8040404@gmail.com>
<47FDBE23.8020402@tweakers.net>
<op.t9dy1aqwcigqcu@apollo13.peufeu.com>
<Pine.LNX.4.64.0804101042050.20402@aragorn.flymine.org>
<47FE27EB.8050804@gmail.com>
<47FE3DC7.4010700@gmail.com>
<47FE4259.7030704@gmail.com>
<20080410130854.f3a3f110.wmoran@collaborativefusion.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsmith@gregsmith.com, wmoran@collaborativefusion.com, john.e.beaver@gmail.com, mendola@gmail.com
Subject: Re: large tables and simple "= constant" queries using indexes
In-Reply-To: <Pine.GSO.4.64.0804101437030.15531@westnet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox