Message-ID: <4CAA5974.2050805@wizmail.org>
Date: Mon, 04 Oct 2010 23:47:16 +0100
From: Jeremy Harris <jgh@wizmail.org>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.8.1.21) Gecko/20090320 Fedora/2.0.0.21-1.fc9
	Thunderbird/2.0.0.21 Mnenhy/0.7.5.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: How does PG know if data is in memory?
References: <AANLkTin5G0jXUbeNdC+1s9LQpk1ZpvDuX-6n90oonen3@mail.gmail.com>
	<4CA9488C.2080502@2ndquadrant.com>
In-Reply-To: <4CA9488C.2080502@2ndquadrant.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/04/2010 04:22 AM, Greg Smith wrote:
> I had a brain-storming session on this subject with a few of the hackers in the community in this area a while back I haven't had a chance to do something with yet (it exists only as a pile of scribbled notes so far). There's a couple of ways to collect data on what's in the database and OS cache, and a couple of ways to then expose that data to the optimizer. But that needs to be done very carefully, almost certainly as only a manual process at first, because something that's producing cache feedback all of the time will cause plans to change all the time, too. Where I suspect this is going is that we may end up tracking various statistics over time, then periodically providing a way to export a mass of "typical % cached" data back to the optimizer for use in plan cost estimation purposes. But the idea of monitoring continuously and always planning based on the most recent data available has some stability issues, both from a "too many unpredictable plan changes" and a "ba
d
> short-term feedback loop" perspective, as mentioned by Tom and Kevin already.

Why not monitor the distribution of response times, rather than "cached" vs. not?

That a) avoids the issue of discovering what was a cache hit  b) deals neatly with
multilevel caching  c) feeds directly into cost estimation.

Cheers,
    Jeremy