Postgresql - Shared buffers analysis

CREATE EXTENSION pg_buffercache;

select usagecount, count(*), isdirty from pg_buffercache
group by isdirty, usagecount order by isdirty, usagecount;

usagecount | count  | isdirty
------------+--------+---------
0 | 167072 | f
1 | 125923 | f
2 |  22361 | f
3 |  14248 | f
4 |  13284 | f
5 | 156717 | f
1 |  11605 | t
2 |   2528 | t
3 |   1996 | t
4 |   1239 | t
5 |   7315 | t

If there is a large number of buffers with high usage count of 4 or 5 your buffers are working well.

This query shows objects (tables and indexes) in cache:

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

This shows how much of relations are in cache:

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_table_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 20

Links:

https://www.postgresql.org/docs/current/static/pgbuffercache.html

ohvitorino/analysis.md