Re: WIP: cross column correlation ...

On Fri, Feb 25, 2011 at 6:41 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> One idea Tom and I kicked around previously is to set an assumed
>> caching percentage for each table based on its size relative to
>> effective_cache_size - in other words, assume that the smaller a table
>> is, the more of it will be cached.  Consider a system with 8GB of RAM,
>> and a table which is 64kB.  It is probably unwise to make any plan
>> based on the assumption that that table is less than fully cached.  If
>> it isn't before the query executes, it soon will be.  Going to any
>> amount of work elsewhere in the plan to avoid the work of reading that
>> table in from disk is probably a dumb idea.  Of course, one downside
>> of this approach is that it doesn't know which tables are hot and
>> which tables are cold, but it would probably still be an improvement
>> over the status quo.
>
> Actually, we *do* have some idea which tables are hot.  Or at least, we
> could.   Currently, pg_stats for tables are "timeless"; they just
> accumulate from the last reset, which has always been a problem in
> general for monitoring.  If we could make top-level table and index
> stats time-based, even in some crude way, we would know which tables
> were currently hot.  That would also have the benefit of making server
> performance analysis and autotuning easier.

I think there would be value in giving the DBA an easier way to see
which tables are hot, but I am really leery about the idea of trying
to feed that directly into the query planner. I think this is one of
those cases where we let people tune it manually for starters, and
then wait for feedback. Eventually someone will say "oh, I never tune
that by hand any more, ever since I wrote this script which does the
following computation... and I just run it out cron". And then we
will get out the party hats. But we will never get the experience we
need to say what that auto-tuning algorithm will be unless we first
provide the knob for someone to fiddle with manually.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company