pg_buffercache report

[NikolayS]

proposal (quick and dirty draft):

with a as (
SELECT reldatabase, c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY 1, 2
ORDER BY 3 DESC
)
select *, sum(buffers) over ()
from a
order by buffers desc
limit 20;

[oleg9301]

Once more with pg_buffercashe(from pg pro)

SELECT c.relname,
  count(*) blocks,
  round( 100.0 * 8192 * count(*) / pg_table_size(c.oid) ) "% of rel",
  round( 100.0 * 8192 * count(*) FILTER (WHERE b.usagecount > 3) / pg_table_size(c.oid) ) "% hot" 
FROM pg_buffercache b
  JOIN pg_class c ON pg_relation_filenode(c.oid) = b.relfilenode
WHERE  b.reldatabase IN (
         0, (SELECT oid FROM pg_database WHERE datname = current_database())
       )
AND    b.usagecount is not null
GROUP BY c.relname, c.oid
ORDER BY 2 DESC
LIMIT 10;

[NikolayS]

thanks @oleg9301!