PostgreSQL 10 High Performance

Top relations in the cache

The following example appears in the documentation as an example for how to use pg_buffercache, and it's quite a good way to start your analysis:

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

Removing the system tables (which has been done on all the examples shown here) shows that almost all of the cache is being used by the pgbench_accounts table and the index enforcing its primary key, as expected, given those accounts are all we were running SELECT statements against:

 relname | buffers ---------------------------------+--------- ...