Re: LRU and full table scans

Hannu Krosing wrote:
>
> On Wed, 2002-02-27 at 13:09, Mike Mascari wrote:
> > On general a discussion has been taking place regarding cached query
> > plans and how MySQL invented them.
>
> IMHO the discussion was about cached queries not query plans.

You're right, of course. It would be interesting though to compare the
speed of a cached query against a cache query plan + cached data blocks.
If the cached query got a hit, the cached query plan + cached data
blocks would lose by the number of cycles spent in the executor.
Alternatively, the cost of a cache miss in the caching of a query means
wasted memory that could have been used for cached data blocks...

>
> > Of course, this is totally false. I
> > remembered a nice paragraph in the Oracle docs as to the process by
> > which Oracle uses shared SQL areas to share the execution plan of
> > identical statements, flushing the area whenever a dependent object was
> > modified. In searching for the reference, however, I stumbled an
> > interesting fact. Unlike normal queries where blocks are added to the
> > MRU end of an LRU list, full table scans add the blocks to the LRU end
> > of the LRU list.
>
> This seems really elegant solution , much better than not caching at all
> and much better than flushing the whole cache by a large table scan

Yes. And Oracle has a CACHE keyword option on its CREATE TABLE/ALTER
TABLE statement to allow full table scans of small lookup tables to
follow normal MRU caching, if necessary.

>
> > I was wondering, in the light of the discussion of
> > using LRU-K, if PostgreSQL does, or if anyone has tried, this technique?
>
> Hannu

Mike Mascari
mascarm(at)mascari(dot)com