On Mon, Sep 30, 2013 at 5:45 AM, Xenofon Papadopoulos <xpapad@gmail.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.