On Fri, Feb 25, 2011 at 6:41 PM, Josh Berkus <j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
