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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us