Subject: Re: What gets cached?
From: Neil Conway <neilc@samurai.com>
To: Martin Nickel <martin@portant.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <pan.2005.10.21.12.34.29.712700@portant.com>
References: <pan.2005.10.21.12.34.29.712700@portant.com>
Content-Type: text/plain
Date: Fri, 21 Oct 2005 17:44:47 -0400
Message-Id: <1129931087.19971.12.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2005-21-10 at 07:34 -0500, Martin Nickel wrote:
> Let's say I do the same thing in Postgres.  I'm likely to have my very
> fastest performance for the first few queries until memory gets filled up.

No, you're not: if a query doesn't hit the cache (both the OS cache and
the Postgres userspace cache), it will run slower. If the caches are
empty when Postgres starts up (which is true for the userspace cache and
might be true of the OS cache), the first queries that are run should be
slower, not faster.

>  The only time Postgres seems to take advantage of cached data is when I
>  repeat the same (or substantially the same) query.

Caching is done on a page-by-page basis -- the source text of the query
itself is not relevant. If two different queries happen to hit a similar
set of pages, they will probably both benefit from the same set of
cached pages.

> I don't know of any way to view what is actually cached at any point
> in time, but it seems like "most recently used" rather than "most
> frequently used".

The cache replacement policy in 7.4 and older releases is simple LRU.
The policy in 8.0 is ARC (essentially a version of LRU modified to try
to retain hot pages more accurately). The policy in 8.1 is a clock-based
algorithm.

-Neil