Thread: Needs Suggestion

Can I calculate the Buffer Hit ratio of a particular query in postgres? That is how many times it finds the required page in its buffer cache, pg_buffercache or the
linux buffer cache.

--
Thank You,
Subham Roy,
CSE IIT Bombay.

On 4/27/2011 9:35 AM, SUBHAM ROY wrote:
> Can I calculate the *Buffer Hit ratio* *of a particular query* in
> postgres? That is *how many times it finds the required page* in its
> buffer cache, pg_buffercache or the
> linux buffer cache.
>
> --
> Thank You,
> Subham Roy,
> CSE IIT Bombay.
>

There are plenty of stats per table, but I dont think you'll find
anything per query.

However, you could clear the table stats, run the query a bunch of
times, then look at the table stats.  Also, it'll tell you pg buffer
hits, but you wont really know if it hit the linux cache or actual hard
drive.

Google can help you out with table stats: postgres cache hit

-Andy

On Wed, Apr 27, 2011 at 16:35, SUBHAM ROY <subham.iem@gmail.com> wrote:
> Can I calculate the Buffer Hit ratio of a particular query in postgres? That
> is how many times it finds the required page in its buffer cache,
> pg_buffercache or the
> linux buffer cache.

You can get the information from the pg cache using
EXPLAIN (ANALYZE, BUFFERS) <your query>

It won't get you the stats from the linux filesystem cache though. You
can perhaps use pgfincore for that in some way.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

I am using postgres 8.4.8, the above command explain(analyze,buffers) is not working. Is there a way to do that.


--
Thank You,
Subham Roy,
CSE IIT Bombay.

On 04/28/2011 02:11 AM, SUBHAM ROY wrote:
> I am using postgres 8.4.8, the above command explain(analyze,buffers) is
> not working. Is there a way to do that.

Nope, the (BUFFERS) syntax was added in 9.0. Try the other suggestions
about using table stats to determine what you want to know.

--
Craig Ringer