Re: pg_statio_all_tables columns

On Mon, Sep 30, 2013 at 5:45 AM, Xenofon Papadopoulos <xpapad(at)gmail(dot)com>wrote:

> I am trying to understand the heap_blks_read and heap_blks_hit of
> pg_statio_all_tables in 9.2
> Do the numbers refer only to SELECT, or they take INSERT into account?
>

They take insert (and update, and delete) into account.

> Would a heap_blks_read / ( heap_blks_read + heap_blks_hit ) ration of over
> 55% combined with a heap_blks_read value of over 50M indicate an issue with
> the queries affecting that table, or it is normal if the table is heavily
> written to?
>

There is really no answer to that. For one thing, some unknown number of
those heap_blks_read are really coming from the OS/FS's page cache, not
from disk. For another thing, we don't know how many queries, of what
kind, on how large of a table, those 50M reads are supporting.

Do you have a performance problem? If so, is it due to IO bottleneck? If
so, high heap_blks_read on a certain table might indicate where the problem
could be (although pg_stat_statements would probably do a better job).

In the absence of a specific problem to be diagnosed, those numbers don't
mean very much.

Cheers,

Jeff