strange inefficiency in shared memory handling

hello,

i have medium sized database ( about 1mln records) served by p2-300 with
64M ram and ide disk. after upgrading to 7.2 version i started to use
pg_stat_* tables to optimize performance, and noticed very strange
thing.

looks like that shared_buffers (which during these tests i tweaked from 256 to 3000) have
great influence on tables blocks, but very small on index ones.

cached index blocks are purged from memory _very_ fast, and frequent
queries which use indexes, results in many disk reads, even if amount of
index-blocks which reside in memory were very small.
of course this memory removal of cached index blocks is caused by normal
queries and their memory (buffer) requirements, but certainly algorithm
which chose which buffer to remove is a very ineffective on index blocks.
looks to me that it preffer table-block too much (especially compared to index ones)

this leads me to conclusion (maybe wrong, i dont know internal buffer
management details) that frequently used buffers which hold tables
elements are correctly marked as important (dont purged from memory) but
buffers which hold index elemens (use at the same ratio as table ones)
are purged from memory very soon.

removal of offending indexes resulted in dropping load on machine from 2.0 to 0.1!!!

below are details (with shared_buffer=3000):

mlot=# select * from pg_statio_user_indexes order by idx_blks_read desc limit 5;
relid | indexrelid | relname | indexrelname | idx_blks_read | idx_blks_hit
-------+------------+---------+------------------+---------------+--------------
67904 | 617937 | kto | kto_nazwa_ukey | 5332 | 16591
67904 | 617935 | kto | kto_kid_ukey | 3103 | 11506
67904 | 67906 | kto | kto_pkey | 2650 | 27648

with buffers <1000 idx_blks_hit was always smaller than idx_blks_read,
even by 10 times;

statio_user_tables showed that hits were larger than reads by
_great_ amount (factor of 2 and more) - so it shows that shared_buffers
are used very well on tables but not on indexes :-(

mlot=# select version();
version
-------------------------------------------------------------------
PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66

table kto has 76 records;

mlot=# \d kto
Table "kto"
Column | Type | Modifiers
----------+--------------------------+-------------------------------------------
id | integer | not null default nextval('seq_kto'::text)
fk_typ | integer | not null
fk_osoba | integer |
kid | integer | not null default nextval('seq_kid'::text)
nazwa | character varying(64) | not null
lid | integer |
pid | integer |
started | timestamp with time zone |
checked | timestamp with time zone |
worked | timestamp with time zone |
version | character varying(32) |
Primary key: kto_pkey
Unique keys: kto_fkosoba_ukey
kto_kid_ukey
kto_nazwa_ukey
Triggers: RI_ConstraintTrigger_618144,
RI_ConstraintTrigger_618142,
RI_ConstraintTrigger_618140,
RI_ConstraintTrigger_618138,
RI_ConstraintTrigger_618136,
RI_ConstraintTrigger_618134

mlot=# \d kto_nazwa_ukey
Index "kto_nazwa_ukey"
Column | Type
--------+-----------------------
nazwa | character varying(64)
unique btree

mlot=# \d kto_kid_ukey
Index "kto_kid_ukey"
Column | Type
--------+-----------------------
kid | integer
unique btree

--
radoslaw.stachowiak.........................................http://alter.pl/