PostgreSQL 10 High Performance

Buffer usage count distribution

This query breaks down usage counts by relation and usagecount, so you can see exactly how usage count distribution differs between tables:

SELECT 
  c.relname, count(*) AS buffers,usagecount 
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.relname,usagecount 
ORDER BY c.relname,usagecount; 

The results make it really obvious what's happening with the tables:

 relname | buffers | usagecount ----------------------------------+---------+------------ pgbench_accounts | 10032 | 0 pgbench_accounts | 10783 | 1 pgbench_accounts | 953 | 2 pgbench_accounts | 66 | 3 pgbench_accounts | 4 | 4 pgbench_accounts_pkey ...