Buffer Analysis

Hi All,

Thought I'd share with the list a small VIEW I've been using to monitor
buffer usage on my systems.  It calculates as a percentage the number of
times the buffer is used instead of the disk.  It relies on the
stats_collector being active at the block level (edit postgresql.conf to set
these values).  The CASE statement is there to avoid divide by zero errors.
The WHERE clause removes any tables for which no access has yet taken place.
These statistics are only worthwhile after a period of normal database use.

Regards,

Tim Knowles


-- SQL

CREATE VIEW buffer_percentageuse AS SELECT relname, ROUND(CASE WHEN
heap_blks_hit = 0 THEN 0 ELSE ((heap_blks_hit::float /
(heap_blks_read::float + heap_blks_hit::float)) * 100) END ,2) as heap,
ROUND(CASE WHEN  idx_blks_hit = 0 THEN 0 ELSE ((idx_blks_hit::float /
(idx_blks_read::float +  idx_blks_hit::float)) * 100) END,2) as index,
ROUND(CASE WHEN toast_blks_hit = 0 THEN 0 ELSE  ((toast_blks_hit::float /
(toast_blks_read::float + toast_blks_hit::float)) * 100)  END,2) as toast
FROM pg_statio_user_tables WHERE heap_blks_read <> 0 or idx_blks_read <> 0
OR toast_blks_read <> 0 ORDER BY relname;


-- You can select * from the entire view to see percentages on a table
basis, or more interestingly calculate averages as follows:

SELECT AVG(heap) FROM buffersize;

SELECT AVG(index) FROM buffersize;