*** Tom Lane <tgl@sss.pgh.pa.us> [Thursday, 30.May.2002, 18:04 -0400]:
> > 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)
> The buffer management code has absolutely no clue which blocks belong
> to indexes and which to tables --- it handles all of them on a uniform
> LRU basis.
thats strange. i do regular vacuum analyze on this database (daily)
although not vacuum full. i dont recreate indexes since upgrade to 7.2
about month ago.
> > 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 :-(
> This seems odd, but I wonder whether it is an artifact of some unusual
> property of your query workload. You haven't offered enough detail to
> let someone else try to reproduce it...
there is rather few queries (10.. 60 per minute) but 6..10
queries (mostly updates) are on this table.
most of those 'kto' table selects were optimized to use indexes .
now when i dropped two of them, pgsql may read all tuples but
since table is small (76 rows) it shouldnt have impact (in fact as i
wrote before, load dropped from about 2 to almost 0).
queries on 'kto' are:
UPDATE kto SET checked=now() WHERE id=1 -- avg 3/minute
UPDATE kto SET checked=now(),worked=now(),lid=460733 WHERE id=4 -- avg 2/minute
UPDATE kto SET lid=460730,worked=now() WHERE id=1 -- avg 1/minute
select nazwa,date_part('epoch',now()-worked),date_part('epoch',now()-checked) from kto where lid is not null -- avg
1/minute
numerical values in the queries are not constant.
--
radoslaw.stachowiak.........................................http://alter.pl/