On Fri, Feb 25, 2011 at 6:41 PM, Josh Berkus <josh@agliodbs.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