[Idea] list cache/disk utilization for each table

[devbreath]

Hello,

Is it possible to make a separate page with information about:

• how many requests were made to each table,
• % of cache hits,
• % of disk hits
• how many requests were processed via disk.
  All this data can be collected using the following SQL query
  with all_tables as ( SELECT * FROM ( SELECT 'all'::text as table_name, sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk, sum( (coalesce(heap_blks_hit,0) + coalesce(idx_blks_hit,0) + coalesce(toast_blks_hit,0) + coalesce(tidx_blks_hit,0)) ) as from_cache FROM pg_statio_all_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables) ) a WHERE (from_disk + from_cache) > 0 -- discard tables without hits ), tables as ( SELECT * FROM ( SELECT relname as table_name, ( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk, ( (coalesce(heap_blks_hit,0) + coalesce(idx_blks_hit,0) + coalesce(toast_blks_hit,0) + coalesce(tidx_blks_hit,0)) ) as from_cache FROM pg_statio_all_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables) ) a WHERE (from_disk + from_cache) > 0 -- discard tables without hits ) SELECT table_name as "table name", from_disk as "disk hits", round((from_disk::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% disk hits", round((from_cache::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% cache hits", (from_disk + from_cache) as "total hits" FROM (SELECT * FROM all_tables UNION ALL SELECT * FROM tables) a ORDER BY (case when table_name = 'all' then 0 else 1 end), "total hits" desc

Thanks in advance!

PS. Sorry, I don't know how to format the SQL code above properly. It looks fine in the editor with all indents.

[ankane]

Hi @devbreath, thanks for the suggestion. However, in the interest of simplicity, it's not something I'd like to add.