Re: How does PG know if data is in memory?

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(at)2ndQuadrant(dot)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