pg_buffercache提供了实时查询shared buffer cache的手段。

在Postgres Plus Advanced Server中使用入下：

edb=# \i /opt/PostgresPlus/8.4AS/share/contrib/pg_buffercache.sql

edb=# select * from pg_buffercache;

 bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usagecount

----------+-------------+---------------+-------------+---------------+----------------+---------+------------

        1 |      176178 |          1663 |       24586 |             0 |              9 | f       |          5

        2 |      176179 |          1663 |       24586 |             0 |              6 | f       |          5

        3 |      176180 |          1663 |       24586 |             0 |              6 | f       |          5

        4 |      176180 |          1663 |       24586 |             0 |              5 | f       |          5

        5 |      176180 |          1663 |       24586 |             0 |              2 | f       |          5

        6 |      176180 |          1663 |       24586 |             0 |              1 | f       |          5

        7 |      176180 |          1663 |       24586 |             0 |              4 | f       |          5

        8 |        1262 |          1664 |           0 |             0 |              0 | f       |          5

        9 |        1260 |          1664 |           0 |             0 |              0 | f       |          5

       10 |        1261 |          1664 |           0 |             0 |              0 | f       |          1

1）查看本库中各个表所占用的buffer大小

edb=# select c.relname,count(*) as buffers                   

from pg_buffercache b,pg_class c                             

where b.relfilenode=c.relfilenode                            

and b.reldatabase =(select oid from pg_database where datname=current_database())                                          

group by c.relname                                           

order by 2 desc;

             relname             | buffers

---------------------------------+---------

 pg_attribute                    |      42

 pg_proc                         |      33

 pg_operator                     |      19

 pg_proc_proname_args_nsp_index  |      15

 pg_class                        |      14

 pg_attribute_relid_attnum_index |      11

 pg_proc_oid_index               |      10

 pg_depend_reference_index       |       9

 pg_type                         |       9

 pg_statistic                    |       7

2）查看脏块的比例

edb=# select count(*) from pg_buffercache a  where a.isdirty='t';

edb=# select count(*) from pg_buffercache a ;