Re: pg_statio_all_tables columns

I do have a performance problem, and it is due to I/O bottleneck.
We don't have pg_stat_statements installed, we will check it out.
Thanks

On Mon, Sep 30, 2013 at 8:44 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> 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
>