On Wed, 20 Jun 2007, PFC wrote:
Except planner hints (argh) I see no way to give this information to the machine... since it's mostly in the mind of the DBA.
And the mind of the DBA has a funny way of being completely wrong some days about what's really happening under the hood.
Maybe a per-table "cache temperature" param (hot, warm, cold), but what about the log table, the end of which is cached, but not the old records ? It's messy.
One of the things that was surprising to me when I started looking at the organization of the PostgreSQL buffer cache is how little gross information about its contents is available. I kept expecting to find a summary section where you could answer questions like "how much of the cache currently has information about index/table X?" used as an input to the optimizer. I understand that the design model expects much of this is unknowable due to the interaction with the OS cache, and in earlier versions you couldn't make shared_buffers big enough for its contents to be all that interesting, so until recently this wasn't worth collecting.
But in the current era, where it's feasible to have multi-GB caches efficiently managed by PG and one can expect processor time is relatively cheap, it seems to me one way to give a major boost to the optimizer is to add some overhead to buffer cache management so it collects such information. When I was trying to do a complete overhaul on the background writer, the #1 problem was that I had to assemble my own statistics on what was inside the buffer cache as it was scanned, because a direct inspection of every buffer is the only way to know things like what percentage of the cache is currently dirty.
I can't figure out if I'm relieved or really worried to discover that Tom isn't completely sure what to do with effective_cache_size either.
-- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
