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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026