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

Thu, 10 Jan 2002 11:55:20 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: again on index usage 
>>>"Zeugswetter Andreas SB SD" said:
 > 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 ?

I suspected this, but haven't really come to test it. On BSD/OS, the buffer 
cache is 10% of the RAM, in my case

buffer cache = 53522432 (51.04 MB)

I guess effective_cache_size = 6400 will be ok.

Daniel


Thu, 10 Jan 2002 13:37:41 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: again on index usage 
[with the new effective_cache_size = 6400]

explain
SELECT sum(input), sum(output) FROM iplog_gate200112
WHERE 
'2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2001-12-02 00:00:00+02' AND 
'2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2002-01-01 00:00:00+02' AND 
ipaddr <<= '193.68.240.0/20' AND 'uni-gw' ~ router;

gives

Aggregate  (cost=56111.97..56111.97 rows=1 width=16)
  ->  Seq Scan on iplog_gate200112  (cost=0.00..56110.54 rows=284 width=16)

takes 3 min to execute. (was 10 sec after fresh restart)

db=# set enable_seqscan to off;

Aggregate  (cost=84980.10..84980.10 rows=1 width=16)
  ->  Index Scan using iplog_gate200112_ipdate_idx on iplog_gate200112  
(cost=0.00..84978.68 rows=284 width=16)

takes 1.8 min to execute. (was 2 sec after fresh reshart)

Still proves my point, But the fresh restart performance is impressive. After 
few minutes the database takes its normal load and in my opinion the buffer 
cache is too much cluttered with pages from other tables.

Which brings another question: with so much RAM recent equipment runs, it may 
be good idea to specifically add to INSTALL instruction on tuning the system 
as soon as it is installed. Most people will stop there, especially after an 
upgrade (as I did).

Daniel


Thu, 10 Jan 2002 14:03:15 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: again on index usage 
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> I cannot really see how 284 rows can have an estimated index cost of 100506 ?

The estimated number of indexscanned rows is more like 50k.  The number
you are looking at includes the estimated selectivity of the
non-indexable WHERE clauses, too.

> What is actually estimated wrong here seems to be the estimated
> effective cache size, and thus the cache ratio of page fetches.

Good point, but I think the estimates are only marginally sensitive
to estimated cache size (if they're not, we have a problem, considering
how poorly we can estimate the kernel's disk buffer size).  It would
be interesting for Daniel to try a few different settings of
effective_cache_size and see how much the EXPLAIN costs change.

			regards, tom lane

Thu, 10 Jan 2002 10:07:11 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: again on index usage 