Re: New/Revised TODO? Gathering actual read performance data for use by planner

Michael Nolan wrote:
> Based on last year's discussion of this TODO item, it seems thoughts 
> have been focused on estimating how much data is
> being satisfied from PG's shared buffers.  However, I think that's 
> only part of the problem.  

Sure, but neither it nor what you're talking about are the real gating 
factor on making an improvement here.  Figuring out how to expose all 
this information to the optimizer so it can use it when planning is the 
hard part.  Collecting a read time profile is just one of the many ways 
you can estimate what's in cache, and each of the possible methods has 
good and bad trade-offs.  I've been suggesting that people assume that's 
a solved problem--I'm pretty sure what you're proposing was done by Greg 
Stark once and a prototype built even--and instead ask what you're going 
to do next if you had this data.

> This data would probably need to be kept separately for each table or 
> index, as some tables or indexes
> may be mostly or fully in cache or on faster physical media than 
> others, although in the absence of other
> data about a specific table or index, data about other relations in 
> the same tablespace might be of some use. 

This is the important part.  Model how the data needs to get stored such 
that the optimizer can make decisions using it, and I consider it easy 
to figure out how it will get populated later.  There are actually 
multiple ways to do it, and it may end up being something people plug-in 
an implementation that fits their workload into, rather than just having 
one available.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us