My PostgreSQL's stuff

You can clone my branches :

git clone http://git.postgresql.org/git/users/c2main/postgres.git

Current fun is provided by adding a column for indexes and tables in pg_statio* views. Those column output the real read on disk: it let calculate the real hit/miss ratio of your full cache (postgresql shared buffers and OS buffer cache)

git checkout -b track_disk origin/track_disk

Example :

cedric=# show track_disk ;
 track_disk 
------------
 on

cedric=# SELECT relname, 
          heap_blks_hit,heap_blks_read,heap_blks_real_read/2 as heap_blks_real_read,
          idx_blks_hit,idx_blks_read,idx_blks_real_read/2 as idx_blks_real_read 
         from pg_statio_user_tables  where relname = 'pgbench_accounts';
-[ RECORD 1 ]-------+-----------------
relname             | pgbench_accounts
heap_blks_hit       | 0
heap_blks_read      | 0
heap_blks_real_read | 0
idx_blks_hit        | 0
idx_blks_read       | 0
idx_blks_real_read  | 0

./pgbench -S -t 100 -c2
tps = 43.698998 (including connections establishing)
tps = 43.785598 (excluding connections establishing)

relname              | pgbench_accounts
heap_blks_hit        | 0
heap_blks_read       | 200
heap_blks_real_read/2| 200
idx_blks_hit         | 342
idx_blks_read        | 262
idx_blks_real_read/2 | 181

--
-- After some runs
--

tps = 99.057743 (including connections establishing)
tps = 99.105170 (excluding connections establishing)

relname              | pgbench_accounts
heap_blks_hit        | 217
heap_blks_read       | 19610
heap_blks_real_read/2| 17663
idx_blks_hit         | 41026
idx_blks_read        | 18570
idx_blks_real_read/2 | 4631

--
-- After some more runs
--

tps = 143.658449 (including connections establishing)
tps = 144.511803 (excluding connections establishing)

relname             | pgbench_accounts
heap_blks_hit       | 442
heap_blks_read      | 39838
heap_blks_real_read | 31023
idx_blks_hit        | 83635
idx_blks_read       | 37372
idx_blks_real_read  | 7112

cedric=# select * from pgmincore('pgbench_accounts');
      relpath       | block_size | block_disk | block_mem | group_mem 
--------------------+------------+------------+-----------+-----------
 base/16384/24598   |       4096 |     262144 |    126140 |     15422
 base/16384/24598.1 |       4096 |      55318 |     26180 |      3228
(2 rows)

cedric=# select * from pgmincore('pgbench_accounts_pkey');
     relpath      | block_size | block_disk | block_mem | group_mem 
------------------+------------+------------+-----------+-----------
 base/16384/24603 |       4096 |      43892 |     43825 |        29

-- 
-- snapshot pgbench_accounts and pgbench_accounts_pkey
-- restart postgresql
-- flush OS cache for pgbench_accounts and pgbench_accounts_pkey
--

cedric=# select * from pgmincore_snapshot('pgbench_accounts');
cedric=# select * from pgmincore_snapshot('pgbench_accounts_pkey');

cedric=# select * from pgfadv_dontneed('pgbench_accounts');
cedric=# select * from pgfadv_dontneed('pgbench_accounts_pkey');

cedric=# select pg_stat_reset();

-- run a pgbench 
./pgbench -S -t 100 -c2
tps = 38.497385 (including connections establishing)
tps = 38.569719 (excluding connections establishing)

-- restore buffer cache
select * from pgfadv_willneed_snapshot('pgbench_accounts');
select * from pgfadv_willneed_snapshot('pgbench_accounts_pkey');

-- run a pgbench 
./pgbench -S -t 100 -c2
tps = 169.629961 (including connections establishing)
tps = 170.889926 (excluding connections establishing)