Re: relation cache statistics (was: -HEAD planner issue wrt hash_joins on dbt3 ?)

On Mon, Sep 18, 2006 at 12:20:10AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jimn(at)enterprisedb(dot)com> writes:
> > I think it'd be better to attack this problem from the "other side";
> > namely looking at what's actually cached.
>
> You can kiss goodbye to plan stability if you go that route... and
> in any case I doubt the assumption that what's in shared buffers is
> representative of what's in kernel cache.

Well, there's 2 issues with caching:

1) Is something we're going to want actually in cache right now?
2) If we need to read something more than once (ie: higher level btree
pages), what are the odds it will still be in cache when we come around
to it the next time.

Once the caches are warmed up, looking at what's actually in them would
give you a very good probability for #1. I suspect that for large
relations, shared_buffers would also match the OS cache pretty well in
most cases, almost certainly better than whatever estimate we're using
now. But I'm not sure how useful that info is to the planner.

For #2 we'd have to know what kind of pressure the caches are under to
replace pages and have some kind of idea how frequently the system is
hitting them. The pg_statio info might be useful there, though
unfortunately in that case I think there's much less likely to be a good
correlation between the two.

If there was *some* way to track stats on page fetches that came out of
the OS cache, I suspect we could make great use of per-relation hit
rates to come up with better plans.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)