Re: again on index usage

This topic seems to come up a lot.  Is there something we are missing in
the FAQ?

---------------------------------------------------------------------------

Zeugswetter Andreas SB SD wrote:
> 
> >  > > > 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.
> 
> Since pg only reads one 8k page at a time, the IO performance of a seq scan is
> probably not nearly a good as it could be when a lot of other IO is done on the
> same drive.
> 
> First thing you should verify is if there is actually a measurable difference
> in IO throughput on the pg drive during the seq scan and the index scan. (iostat)
> If there is not, then random_page_cost is too high in your scenario.
> (All assuming your data is not still clustered like Tom suggested)
> 
> Andreas
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026