Good afternoon,
Relatively new to PostgreSQL and have been assigned the task of capturing cache I/O % hits. I figured out (thanks to other posts) how to turn on the capture and what stats to (kind of) capture. I did find a view in the archives as shown below but it does not execute, error follows. I'm using 8.0.1 so that shouldn't be the issue. Any help will be appreciated.
Also, I also found pg_reset_stats.tar.gz in the archives with a lot of talk regarding its addition as a patch, did it ever make it in? If not, can I get a copy of it somewhere? The tar.gz gets corrupted when I download it.
Thank you,
Tim
CREATE VIEW cache_hits 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
union select 'ALL TABLES', ROUND(CASE WHEN sum(heap_blks_hit) = 0 THEN 0
ELSE ((sum(heap_blks_hit::float) /(sum(heap_blks_read::float) +
sum(heap_blks_hit::float))) * 100) END ,2) as heap, ROUND(CASE WHEN
sum(idx_blks_hit) = 0 THEN 0 ELSE ((sum(idx_blks_hit::float)
/(sum(idx_blks_read::float) + sum(idx_blks_hit::float))) * 100) END,2) as
index,ROUND(CASE WHEN sum(toast_blks_hit) = 0 THEN 0 ELSE
((sum(toast_blks_hit::float) /(sum(toast_blks_read::float) +
sum(toast_blks_hit::float))) * 100) END,2) as toast FROM
pg_statio_user_tables HAVING sum(heap_blks_read) <> 0 or sum(idx_blks_read)
<> 0 OR sum(toast_blks_read) <> 0 ;
ERROR: function round(double precision, integer) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.
