Re: Performance TODO items

> So why do we cache sequetially-read pages? Or at least not have an
> option to control it?
>
> Oracle (to the best of my knowledge) does NOT cache pages read by a
> sequential index scan for at least two reasons/assumptions (two being
> all that I can recall):
>
> 1. Caching pages for sequential scans over sufficiently large tables
> will just cycle the cache. The pages that will be cached at the end of
> the query will be the last N pages of the table, so when the same
> sequential query is run again, the scan from the beginning of the table
> will start flushing the oldest cached pages which are more than likely
> going to be the ones that will be needed at the end of the scan, etc,
> etc. In a multi-user environment, the effect is worse.
>
> 2. Concurrent or consective queries in a dynamic database will not
> generate plans that use the same sequential scans, so they will tend to
> thrash the cache.
>
> Now there are some databases where the same general queries are run time
> after time and caching the pages from sequential scans does make sense,
> but in larger, enterprise-type systems, indices are created to help
> speed up the most used queries and the sequential cache entries only
> serve to clutter the cache and flush the useful pages.
>
> Is there any way that caching pages read in by a sequential scan could
> be made a configurable-option?
>
> Any chance someone could run pgbench on a test system set up to not
> cache sequential reads?

Yep, that is the issue. If the whole table fits in the cache, it is
great. If not, it is useless or worse because it forces out other
pages. Right now the cache is oldest-out and doesn't know anything
about access patterns. We would need to get that info passed in the
cache, probably some function parameter.

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