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.

> 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

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

------------
Hannu

27 Feb 2002 10:43:11 +0500
Hannu Krosing <hannu@krosing.net>
Re: LRU and full table scans
On general a discussion has been taking place regarding cached query
plans and how MySQL invented them. 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. I was wondering, in the light of the discussion of
using LRU-K, if PostgreSQL does, or if anyone has tried, this technique?

Mike Mascari
mascarm@mascari.

Wed, 27 Feb 2002 03:09:45 -0500
Mike Mascari <mascarm@mascari.com>
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@mascari.com

Wed, 27 Feb 2002 07:22:08 -0500
Mike Mascari <mascarm@mascari.com>
Re: LRU and full table scans
Mike Mascari wrote:
> On general a discussion has been taking place regarding cached query
> plans and how MySQL invented them. 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. I was wondering, in the light of the discussion of
> using LRU-K, if PostgreSQL does, or if anyone has tried, this technique?

Yes, someone from India has a project to test LRU-K and MRU for large
table scans and report back the results.  He will implement whichever is
best.  He posted a week ago, see "Implementation Proposal For Add Free
Behind Capability For Large Sequential Scan", Amit Kumar Khare
<skamit2000@yahoo.com>.

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

Wed, 27 Feb 2002 22:42:21 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: LRU and full table scans
"Mike Mascari" <mascarm@mascari.com> escribi� en el mensaje
news:3C7C9449.B747532B@mascari.com...
> On general a discussion has been taking place regarding cached query
> plans and how MySQL invented them. 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. I was wondering, in the light of the discussion of
> using LRU-K, if PostgreSQL does, or if anyone has tried, this technique?
>
> Mike Mascari
> mascarm@mascari.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html


First Hello, Second...Yes, I'm doing some studies on the buffer manager and
also studying and implementing different policys, one of them is the LRU-K.

By the time I don't have any results, I'm just preparing to run the TPC-H
benchmark to test all the policys that I have implemented.
I have implemented some policys for the buffer manager, some better and some
worst than LRU (FIFO, LFU, LRD, FBR, LRU-K, LRFU, 4 CLOCK policys (or second
chance), CORRELATED REFERENCES, and different combinations of them like
(LFU+CLOCK+AGING(by division or by substraction)+CORRELATED REFERENCES)),
there are seven principal policys and seven "add-on's" that could be applyed
to them, resulting in 25 combinations of policys.





Wed, 6 Mar 2002 23:09:51 +0100
"Roque Bonilla" <roque.bonilla@wanadoo.es>
Re: LRU and full table scans
Roque Bonilla wrote:
> First Hello, Second...Yes, I'm doing some studies on the buffer manager and
> also studying and implementing different policys, one of them is the LRU-K.
> 
> By the time I don't have any results, I'm just preparing to run the TPC-H
> benchmark to test all the policys that I have implemented.
> I have implemented some policys for the buffer manager, some better and some
> worst than LRU (FIFO, LFU, LRD, FBR, LRU-K, LRFU, 4 CLOCK policys (or second
> chance), CORRELATED REFERENCES, and different combinations of them like
> (LFU+CLOCK+AGING(by division or by substraction)+CORRELATED REFERENCES)),
> there are seven principal policys and seven "add-on's" that could be applyed
> to them, resulting in 25 combinations of policys.

We do have someone working on LRU-K but we haven't seen any test results
from him yet.  Please let us know what you find.

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

Wed, 13 Mar 2002 13:27:33 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: LRU and full table scans