
> > 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.

Well, the number I told him (29370) should clearly prefer the index.
The estimate is very sensitive to this value :-(
With 29370 (=229 Mb) the index cost is 1,364 instead of 3,887 with the 
default of 1000 pages ==> index scan.

229 Mb file cache with 512Mb Ram is a reasonable value, I have
a lot more here:
Memory    Real     Virtual
free        0 MB    218 MB
procs      95 MB    293 MB
files     159 MB
total     256 MB    512 MB

Andreas

Thu, 10 Jan 2002 17:04:29 +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.
 > > 
 > > 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.
 > 
 > Well, the number I told him (29370) should clearly prefer the index.
 > The estimate is very sensitive to this value :-(
 > With 29370 (=229 Mb) the index cost is 1,364 instead of 3,887 with the 
 > default of 1000 pages ==> index scan.

But... if I understand it right (effective_cache_size to be related to kernel 
buffer space). it turns out that the estimates are different with reality - my 
buffer cache is ca. 50 MB and I still get at least twice the performance with 
index scan instead of sequential scan - where as Tom explained things should 
be much worse.

I considered the possibility that the clustered table can still maintain some 
ordering by ipdate after being clustered by ipaddr - but with over 65k ip 
addresses, almost evenly spread, this should be not so significant.

Best Regards,
Daniel


Thu, 10 Jan 2002 18:42:13 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: again on index usage 