postgresql pg_buffercache(二)
ers_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;
relname | buffered | buffers_percent | percent_of_relation
----------------------------------+----------+-----------------+---------------------
pg_statistic | 120 kB | 0.4 | 100.0
pg_depend | 104 kB | 0.3 | 29.5
pg_operator | 104 kB | 0.3 | 100.0
pg_depend_reference_index | 104 kB | 0.3 | 50.0
pg_rewrite | 64 kB | 0.2 | 66.7
pg_operator_oid_index | 32 kB | 0.1 | 100.0
pg_statistic_relid_att_inh_index | 32 kB | 0.1 | 100.0
pg_operator_oprname_l_r_n_index | 40 kB | 0.1 | 100.0
pg_depend_depender_index | 48 kB | 0.1 | 22.2
pg_amop_fam_strat_index | 32 kB | 0.1 | 100.0
缓冲区使用分布：
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;
relname | buffers | usagecount
-----------------------------------+---------+------------
pg_aggregate | 1 | 5
pg_aggregate_fnoid_index | 1 | 4
pg_aggregate_fnoid_index | 1 | 5
pg_am | 1 | 5
pg_amop | 3 | 5
pg_amop_fam_strat_index | 1 | 1
pg_amop_fam_strat_index | 3 | 5
pg_amop_opr_fam_index | 3 | 5
pg_amproc | 1 | 4
pg_amproc | 1 | 5
pg_amproc_fam_proc_index | 2 | 5
pg_attrdef | 1 | 3
pg_attrdef_adrelid_adnum_index | 2 | 3
pg_attrdef_oid_index | 1 | 1
pg_attrdef_oid_index | 1 | 2
pg_cast | 2 | 5
pg_cast_source_target_index | 2 | 5
pg_collation | 1 | 1
pg_collation_oid_index | 1 | 3
pg_collation_oid_index | 2 | 5
pg_constraint | 1 | 1
pg_default_acl_role_nsp_obj_index | 1 | 5
pg_depend | 3 | 1
pg_depend | 1 | 2
pg_depend | 9 | 5
pg_depend_depender_index | 1 | 4
pg_depend_depender_index | 5 | 5
pg_depend_reference_index | 2 | 1
pg_depend_reference_index | 1 | 2
pg_depend_reference_index | 1 | 4
pg_depend_reference_index | 9 | 5