Re: How to find if a SELECT is reading from buffer or disk ?

On Tue, May 25, 2010 at 1:48 PM, Balkrishna Sharma <b_ki(at)hotmail(dot)com> wrote:

> Hi,
>
> I am increasing the shared_buffer size in postgresql.conf and want to
> measure its effect on READ. In essence I want to know if the SELECT queries
> I am firing repeatedly is reading from the buffer or going directly to the
> disk.
>
> I am expecting the first SELECT to go to disk and the subsequent call of
> the same SELECT to read from buffer .
>
> Right now I am just looking at execution time of the SELECTs and trying to
> conclude. But there should be a direct way to see where the SELECT reads
> from.
>

You can also use pg_stat_database view. you can compute cache reads
percentage of the total number of reads (cache and physical) between the two
snapshots using pg_stat_database.blks_hit and pg_stat_database.blks_read.

Chirag Dave 416-673-4102
Database Administrator, Afilias Canada Corp.
cdave(at)ca(dot)afilias(dot)info

> How can I accomplish this ?
>
> Thanks
> Bala
>
> ------------------------------
> The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with
> Hotmail. Get busy.<http://www.windowslive.com/campaign/thenewbusy?tile=multicalendar&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5>
>