Re: Index Scans become Seq Scans after VACUUM ANALYSE

Lincoln Yeoh wrote:
> At 10:48 AM 4/18/02 -0400, mlw wrote:
> >Bruce Momjian wrote:
> > >
> > > Have you tried reducing 'random_page_cost' in postgresql.conf.  That
> > > should solve most of your problems if you would like more index scans.
> >
> >My random page cost is 1 :-)
> 
> What happens when you set random page cost to 1? Between an index scan of 
> 50% of a table and a full table scan which would the optimizer pick? With 
> it at 1, what percentage would be the switchover point?
> 
> Because I'm thinking that for _repeated_ queries when there is caching the 
> random page cost for "small" selections may be very low after the first 
> very costly select (may not be that costly for smart SCSI drives). So 
> selecting 10% of a table randomly may not be that costly after the first 
> select. Whereas for sequential scans 100% of the table must fit in the 
> cache. If the cache is big enough then whichever results in selecting less 
> should be faster ( noting that typically sequential RAM reads are faster 
> than random RAM reads ). If the cache is not big enough then selecting less 
> may be better up till the point where the total amount repeatedly selected 
> cannot be cached, in which case sequential scans should be better. This is 
> of course for queries in serial, not queries in parallel. How would one 
> take these issues into account in an optimizer?

This is an interesting point, that an index scan may fit in the cache
while a sequential scan may not.  I can see cases where even a index
scan of a large percentage of the table may win over an sequential scan.
Interesting.

Determining that, especially in a multi-user environment, is quite
difficult.

We do have 'effective_cache_size', which does try to determine how much
of the I/O will have to go to disk and how much may fit in the cache,
but it is quite a fuzzy number.

-- 
  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, Pennsylvania 19026