Message-ID: <4CA949FF.4000200@2ndquadrant.com>
Date: Sun, 03 Oct 2010 23:29:03 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Craig Ringer <craig@postnewspapers.com.au>
CC: =?ISO-8859-1?Q?Fabr=EDcio_dos_Anjos_Silva?=
	<fabricio.silva@linkcom.com.br>, pgsql-performance@postgresql.org
Subject: Re: How does PG know if data is in memory?
References: <AANLkTin5G0jXUbeNdC+1s9LQpk1ZpvDuX-6n90oonen3@mail.gmail.com>
	<AANLkTik5hnOcU=spN0wmWmR6_wHN=sKfVNzJeDuPTVW_@mail.gmail.com>
	<AANLkTinrtVbjtadteR88xWGGYovAzhgOWR1=pXLDi08M@mail.gmail.com>
	<4CA5D2F5.5070900@postnewspapers.com.au>
In-Reply-To: <4CA5D2F5.5070900@postnewspapers.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Craig Ringer wrote:
> If some kind of cache awareness was to be added, I'd be interested in 
> seeing a "hotness" measure that tracked how heavily a given 
> relation/index has been accessed and how much has been read from it 
> recently. A sort of age-scaled blocks-per-second measure that includes 
> both cached and uncached (disk) reads. This would let the planner know 
> how likely parts of a given index/relation are to be cached in memory 
> without imposing the cost of tracking the cache in detail. I'm still 
> not sure it'd be all that useful, though...

Yup, that's one of the design ideas scribbled in my notes, as is the 
idea of what someone dubbed a "heat map" that tracked which parts of the 
relation where actually the ones in RAM, the other issue you mentioned.  
The problem facing a lot of development possibilities in this area is 
that we don't have any continuous benchmarking of complicated plans 
going on right now.  So if something really innovative is done, there's 
really no automatic way to test the result and then see what types of 
plans it improves and what it makes worse.  Until there's some better 
performance regression work like that around, development on the 
optimizer has to favor being very conservative.

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book