使用pg_buffercache插件可查看shared buffer中的内容. 安装pg_buffercache ``` [pg12@localhost pg_buffercache]$ make make -C ../../src/backend generated-headers make[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/utils' make[1]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend' gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o pg_buffercache_pages.o pg_buffercache_pages.c -MMD -MP -MF .deps/pg_buffercache_pages.Po gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -shared -o pg_buffercache.so pg_buffercache_pages.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/appdb/pg12/pg12beta3/lib',--enable-new-dtags [pg12@localhost pg_buffercache]$ sudo make install [sudo] password for pg12: make -C ../../src/backend generated-headers make[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/utils' make[1]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend' /bin/mkdir -p '/appdb/pg12/pg12beta3/lib/postgresql' /bin/mkdir -p '/appdb/pg12/pg12beta3/share/postgresql/extension' /bin/mkdir -p '/appdb/pg12/pg12beta3/share/postgresql/extension' /bin/install -c -m 755 pg_buffercache.so '/appdb/pg12/pg12beta3/lib/postgresql/pg_buffercache.so' /bin/install -c -m 644 ./pg_buffercache.control '/appdb/pg12/pg12beta3/share/postgresql/extension/' /bin/install -c -m 644 ./pg_buffercache--1.2.sql ./pg_buffercache--1.2--1.3.sql ./pg_buffercache--1.1--1.2.sql ./pg_buffercache--1.0--1.1.sql ./pg_buffercache--unpackaged--1.0.sql '/appdb/pg12/pg12beta3/share/postgresql/extension/' [pg12@localhost pg_buffercache]$ ``` 简单使用 ``` [local]:5432 pg12@testdb=# create extension pg_buffercache pg12@testdb-# ; CREATE EXTENSION Time: 149.794 ms [local]:5432 pg12@testdb=# \d pg_buffercache View "public.pg_buffercache" Column | Type | Collation | Nullable | Default ------------------+----------+-----------+----------+--------- bufferid | integer | | | relfilenode | oid | | | reltablespace | oid | | | reldatabase | oid | | | relforknumber | smallint | | | relblocknumber | bigint | | | isdirty | boolean | | | usagecount | smallint | | | pinning_backends | integer | | | [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# select * from pg_buffercache; -[ RECORD 1 ]----+------ bufferid | 1 relfilenode | 33029 reltablespace | 1664 reldatabase | 0 relforknumber | 0 relblocknumber | 0 isdirty | f usagecount | 5 pinning_backends | 0 -[ RECORD 2 ]----+------ bufferid | 2 relfilenode | 32825 reltablespace | 1664 reldatabase | 0 relforknumber | 0 relblocknumber | 0 isdirty | f usagecount | 4 pinning_backends | 0 ... ``` 直接查询pg_buffercache可获得shared buffer的相关信息. 创建统计视图 ``` create or replace view vw_buffercache_hogs as select case when pg_buffercache.reldatabase = 0 then '- global' when pg_buffercache.reldatabase <> (select pg_database.oid from pg_database where pg_database.datname = current_database()) then '- database ' || quote_literal(pg_database.datname) when pg_namespace.nspname = 'pg_catalog' then '- system catalogues' when pg_class.oid is null and pg_buffercache.relfilenode > 0 then '- unknown file ' || pg_buffercache.relfilenode when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+$' then (substring(pg_class.relname, 10)::oid)::regclass || ' TOAST'::text when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+_index$' then ((rtrim(substring(pg_class.relname, 10), '_index'))::oid)::regclass || ' TOAST index' else pg_class.oid::regclass::text end as key, count(*) as buffers, sum(case when pg_buffercache.isdirty then 1 else 0 end) as dirty_buffers, round(count(*) / (SELECT pg_settings.setting FROM pg_settings WHERE pg_settings.name = 'shared_buffers')::numeric, 4) as hog_factor from pg_buffercache left join pg_database on pg_database.oid = pg_buffercache.reldatabase left join pg_class on pg_class.relfilenode = pg_buffercache.relfilenode left join pg_namespace on pg_namespace.oid = pg_class.relnamespace group by 1 order by 2 desc; ``` 查询该视图 ``` [local]:5432 pg12@testdb=# create or replace view vw_buffercache_hogs as pg12@testdb-# select case pg12@testdb-# when pg_buffercache.reldatabase = 0 pg12@testdb-# then '- global' pg12@testdb-# when pg_buffercache.reldatabase <> (select pg_database.oid from pg_database where pg_database.datname = current_database()) pg12@testdb-# then '- database ' || quote_literal(pg_database.datname) pg12@testdb-# when pg_namespace.nspname = 'pg_catalog' pg12@testdb-# then '- system catalogues' pg12@testdb-# when pg_class.oid is null and pg_buffercache.relfilenode > 0 pg12@testdb-# then '- unknown file ' || pg_buffercache.relfilenode pg12@testdb-# when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+$' pg12@testdb-# then (substring(pg_class.relname, 10)::oid)::regclass || ' TOAST'::text pg12@testdb-# when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+_index$' pg12@testdb-# then ((rtrim(substring(pg_class.relname, 10), '_index'))::oid)::regclass || ' TOAST index' pg12@testdb-# else pg_class.oid::regclass::text pg12@testdb-# end as key, pg12@testdb-# count(*) as buffers, sum(case when pg_buffercache.isdirty then 1 else 0 end) as dirty_buffers, pg12@testdb-# round(count(*) / (SELECT pg_settings.setting FROM pg_settings WHERE pg_settings.name = 'shared_buffers')::numeric, 4) as hog_factor pg12@testdb-# from pg_buffercache pg12@testdb-# left join pg_database on pg_database.oid = pg_buffercache.reldatabase pg12@testdb-# left join pg_class on pg_class.relfilenode = pg_buffercache.relfilenode pg12@testdb-# left join pg_namespace on pg_namespace.oid = pg_class.relnamespace pg12@testdb-# group by 1 pg12@testdb-# order by 2 desc; CREATE VIEW Time: 69.892 ms [local]:5432 pg12@testdb=# select * from vw_buffercache_hogs; key | buffers | dirty_buffers | hog_factor --------------------------+---------+---------------+------------ | 65187 | 0 | 0.9947 - system catalogues | 174 | 22 | 0.0027 - unknown file 32856 | 32 | 1 | 0.0005 - unknown file 32861 | 28 | 2 | 0.0004 - global | 19 | 0 | 0.0003 - unknown file 32869 | 15 | 4 | 0.0002 - unknown file 32868 | 11 | 1 | 0.0002 t_copy | 8 | 0 | 0.0001 - unknown file 32867 | 8 | 1 | 0.0001 - unknown file 32860 | 8 | 1 | 0.0001 - unknown file 32873 | 7 | 2 | 0.0001 - unknown file 32809 | 7 | 1 | 0.0001 - unknown file 32816 | 6 | 3 | 0.0001 - unknown file 32872 | 5 | 1 | 0.0001 pg_rewrite TOAST | 4 | 3 | 0.0001 - unknown file 32815 | 4 | 1 | 0.0001 - unknown file 32874 | 4 | 1 | 0.0001 - unknown file 32859 | 3 | 1 | 0.0000 pg_rewrite TOAST index | 2 | 1 | 0.0000 pg_statistic TOAST index | 2 | 0 | 0.0000 t_import | 1 | 0 | 0.0000 pg_statistic TOAST | 1 | 0 | 0.0000 (22 rows) Time: 201.894 ms ``` 另外,关于索引创建后,索引数据是否已缓存在shared buffer,答案是否定的. ``` [local]:5432 pg12@testdb=# create index idx_t_prewarm_id on t_prewarm(id); CREATE INDEX Time: 578.582 ms [local]:5432 pg12@testdb=# select * from vw_buffercache_hogs; key | buffers | dirty_buffers | hog_factor ------------------------+---------+---------------+------------ | 59920 | 0 | 0.9143 t_prewarm | 5406 | 0 | 0.0825 - system catalogues | 82 | 6 | 0.0013 - unknown file 32856 | 32 | 2 | 0.0005 - unknown file 32861 | 19 | 0 | 0.0003 - unknown file 32869 | 14 | 1 | 0.0002 - global | 14 | 0 | 0.0002 - unknown file 32867 | 8 | 0 | 0.0001 - unknown file 32860 | 8 | 1 | 0.0001 - unknown file 32872 | 5 | 1 | 0.0001 - unknown file 32873 | 4 | 1 | 0.0001 - unknown file 32816 | 4 | 0 | 0.0001 - unknown file 32868 | 4 | 0 | 0.0001 - unknown file 32859 | 3 | 1 | 0.0000 - unknown file 32809 | 3 | 0 | 0.0000 - unknown file 32815 | 3 | 0 | 0.0000 - unknown file 32874 | 3 | 1 | 0.0000 pg_rewrite TOAST index | 2 | 0 | 0.0000 pg_rewrite TOAST | 2 | 0 | 0.0000 (19 rows) Time: 221.542 ms ``` 在预热后才会在内存中 ``` [local]:5432 pg12@testdb=# select pg_prewarm('idx_t_prewarm_id'); pg_prewarm ------------ 2745 (1 row) Time: 51.211 ms [local]:5432 pg12@testdb=# select * from vw_buffercache_hogs; key | buffers | dirty_buffers | hog_factor ------------------------+---------+---------------+------------ | 62601 | 0 | 0.9552 idx_t_prewarm_id | 2745 | 0 | 0.0419 - system catalogues | 69 | 0 | 0.0011 - unknown file 32856 | 31 | 0 | 0.0005 - unknown file 32861 | 18 | 0 | 0.0003 - global | 14 | 0 | 0.0002 - unknown file 32869 | 11 | 0 | 0.0002 - unknown file 32860 | 8 | 0 | 0.0001 - unknown file 32867 | 8 | 0 | 0.0001 - unknown file 32872 | 5 | 0 | 0.0001 - unknown file 32816 | 4 | 0 | 0.0001 t_prewarm | 4 | 0 | 0.0001 - unknown file 32873 | 4 | 0 | 0.0001 - unknown file 32815 | 3 | 0 | 0.0000 - unknown file 32868 | 3 | 0 | 0.0000 - unknown file 32809 | 3 | 0 | 0.0000 pg_rewrite TOAST index | 2 | 0 | 0.0000 pg_rewrite TOAST | 2 | 0 | 0.0000 - unknown file 32874 | 1 | 0 | 0.0000 (19 rows) Time: 131.575 ms ``` **参考资料** [Postgresql cache (memory) performance + how to warm up the cache](https://stackoverflow.com/questions/934360/postgresql-cache-memory-performance-how-to-warm-up-the-cache)