Re: again on index usage

Daniel wrote: (stripped to info I used)
> NOTICE: Pages 17722: Changed 0, reaped 0, Empty 0, New 0;
> Tup 1706202: Vac 0,
> NOTICE: Index iplog_test_ipaddr_idx: Pages 5621; Tuples 1706202. CPU
> NOTICE: Index iplog_test_ipdate_idx: Pages 4681; Tuples 1706202. CPU

> -> Seq Scan on iplog_test (cost=0.00..56111.54 rows=284 width=16)
> query runs for ca 3.5 minutes.

> -> Index Scan using iplog_test_ipdate_idx on iplog_test
> (cost=0.00..100505.94 rows=284 width=16)
> query runs for ca 2.2 minutes.

I cannot really see how 284 rows can have an estimated index cost of 100506 ?

> 512 MB RAM, with 15000 RPM Cheetah for the database, running

> Perhaps I need to tune this machine's costs to prefer more
> disk intensive operations over CPU intensive operations?

What is actually estimated wrong here seems to be the estimated
effective cache size, and thus the cache ratio of page fetches.
Most of your pages will be cached.

The tuning parameter is: effective_cache_size

With (an estimated) 50 % of 512 Mb for file caching that number would
need to be:
effective_cache_size = 32768 # 8k pages

Can you try this and tell us what happens ?

Andreas