PostgreSQL 10 High Performance - Third Edition

Buffer content summary with percentages

This query is where you probably want to start when analyzing a new database. It lets you quickly see not just how much data is being cached for each table, but how much that represents relative to its total size:

SELECT 
  c.relname, 
  pg_size_pretty(count(*) * 8192) as buffered, 
  round(100.0 * count(*) /  
    (SELECT setting FROM pg_settings 
      WHERE name='shared_buffers')::integer,1) 
    AS buffers_percent, 
  round(100.0 * count(*) * 8192 /  
    pg_relation_size(c.oid),1) 
    AS percent_of_relation 
FROM pg_class c 
  INNER JOIN pg_buffercache b 
    ON b.relfilenode = c.relfilenode 
  INNER JOIN pg_database d 
    ON (b.reldatabase = d.oid AND d.datname = current_database()) 
GROUP BY c.oid,c.relname 
ORDER BY 3 DESC 
LIMIT 10; 

pg_relation_size() ...