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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
