Hello,
I'm currently have to investigate some time to anyalyze how often the database
has to read data from disk compared to the number of cache(shared buffer) accesses.
I got the following key figures for an example table:
a) pg_statio_user_indexes=>
-[ RECORD 12 ]+----------------------------------
relid | 42535
...
idx_blks_read | 20504593
idx_blks_hit | 17756649
-[ RECORD 14 ]+----------------------------------
relid | 42535
...
idx_blks_read | 146942531
idx_blks_hit | 48752405641
Regarding "RECORD 12" I can't explain why the number of blks_read is higher than
the number of blks_hit. Why should a page be read if we can't find a requested
tuple there ?
The numbers in "RECORD 14" seems O.K., we read some pages and we find several
tuples we need in each page (for average). Is this assumption correct ?
b) pg_statio_user_tables=>
-[ RECORD 2 ]---+------------
relid | 42535
..
heap_blks_read | 1572252620
heap_blks_hit | 32724990601
idx_blks_read | 197453378
idx_blks_hit | 49240726062
toast_blks_read | 0
toast_blks_hit | 0
tidx_blks_read | 0
tidx_blks_hit | 0
heap_blks_read is the number of disk blocks read for that table (excluding index
access), does heap_blks_hit mean the number of accesses to the cache for that data ?
...and is the number of heap_blks_read in heap_blks_hit included, or is this
number the additional accesses, after reading the data from disk to buffer ?
Let me try to explain my question with the numbers of the example table:
number of disk reads = 1572252620
number of cache reads = 32724990601 OR (32724990601 - 1572252620) ???
any help appreciated....
many thanks in advance...GERD..
